NebRef
river guide
Posts:91
 |
| 11-09-2012 03:25 PM |
|
I just ran through the instructions in the Demonstration #14 tutorial and it really works well. A couple of questions:
1. My table is not populating the DSParam1 field automatically as I believe it should. I have attached an image of what my table looks like. What do I need to do to populate this field as records are entered?
2. When I run this query against my table, I get the message "Query did not return any data"
select * from dbo.DynamicForms_ClubMeetingInfo
How do I get this query to return the data in my form results table so that I can run queries against the data in other Dynamic Views. Note that if I can view my table data if I create a Dynamic View instance set to "Dynamic Forms Instance" as the Data Source.
This is really an amazing feature. I hope I can tweak it a bit so I can run queries against the table. Thank you.
Jim |
|
|
|
|
NebRef
river guide
Posts:91
|
| 11-10-2012 06:00 AM |
|
Folks:
I went through all of the directions again. I found that I had not changed the table name in my InsertData SQL Statement in the completion event. It is updating the DynamicForms_ResultsID field now as it is supposed to!!
I can run queries against the table now using that field to specify a record. Thanks for the tutorial - really very good!
Jim |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 11-12-2012 04:46 AM |
|
Hello Jim, Glad to see you've figured this out  Let us know if you have any questions. Thanks, -Ryan |
|
|
|
|
NebRef
river guide
Posts:91
|
| 11-12-2012 12:46 PM |
|
Ryan: Since you asked. I generated the form as described in this Demonstration and everything works well. I use Dynamic Viewer with the table as the reference source and when the form is updated, the table correctly displays only the most current entry for that UniqueCompletionId. However, if I run an SQL query against the table, it returns all of the table contents and duplicate entries for the same UniqueCompletionID. In order to avoid this problem, I have had to access my table using SQL Server Management Studio and delete the records with the old DynamicForms_ResultsID. Is there any way that I can modify the SQL statement to return only the most recent entry for the same UniqueCompletionID? Is there a scheduling event I can run to delete the "old" entries in the table? Thanks for your help. Jim |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 11-13-2012 05:16 AM |
|
Hello Jim,
You may want to use the "top 1" keyword along with an "Order by" clause descending order. If you have a Date Submission column, you can use the value in this column for the Order By to determine the most recently submitted.
Example:
Select top 1 ColumnName from TableName order by DateColumn DESC
Please let me know if you have any questions.
Thanks,
Ryan
|
|
|
|
|
NebRef
river guide
Posts:91
|
| 11-16-2012 03:56 AM |
|
Ryan:
Thanks for the suggestion. Unfortunately, this did not work for me.
My problem is that my table has a total of 8 users and 28 records. However, the records are not evenly distrubuted across users. One user has only one record while User #4 has 5 records in the table.
If I sort the records by my datetimecolumn, I find that User #4 has 3 of the eight most recent records. Therefore, I can't order by datetimecolumn and then take the Top8 hoping that I'll get the most recent record for each of my eight users.
My problem seems to be that my form is not updating records and overwriting existing records when there is an edit. Instead, every "edit" shows up in the table as an add. Is my form set up incorrectly?
Thanks for your help,
Jim |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 11-26-2012 06:34 AM |
|
Hello Jim,
Sounds like you need to have another SQL Completion Event. One that handles INSERTING, and another that handles UPDATING.
We'll now you may be wondering how to distinguish which one is enabled? Well you can do so by simply using a Boolean value "True" or "False".
For instance, you can add a hidden field to your Form and retrieve value from a querystring variable and it will have a Default value of False.
Your INSERT SQL Completion Event will only fire if the hidden field value = "False", the default.
Your UPDATE SQL Completion Event will only fire the the hidden field value ="True", value that needs to be passed in via the URL(can do so simply by redirecting to the same page but by hardcoding the Querystring name/value pair).
Regarding the SQL Event that isn't working for you, you can use the following query:
Select top 1 ColumnName from TableName where UserID = '$(UserID)' order by DateColumn DESC
Let me know if you have any questions.
Thanks,
Ryan
|
|
|
|
|
NebRef
river guide
Posts:91
|
| 12-10-2012 10:12 AM |
|
Ryan:
Thanks for the help. Still trying to refine this process.
One issue - whenever I change the column name "DynamicForms_ResultsID" to something else, my form no longer updates. Is there a way I can either (1) change this column name, or (2) copy this column's contents into another column with a name I can use.
Please let me know.
Jim |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 12-10-2012 11:50 AM |
|
Hello NebRef,
The DynamicForms_ResultsID is the primary key of your table. Well sort of a Compound(dual) Primary key. The DynamicForms_ResultsID and UniqueCompletionID are both primary keys in their own sense.
If you're updating the column name, anywhere you reference this deprecated column name, you'll need to modify to reference the new column name.
You're SQL is more than likely throwing an error and that's why the update isn't taking effect.
Please revise the columns that you're referencing to ensure that you're referencing the correct columns. If you reference a column name that doesn't exist, your entire SQL statement will fail.
Please review, test, and let me know if you have any questions.
Thanks,
Ryan |
|
|
|
|
NebRef
river guide
Posts:91
|
| 12-11-2012 12:57 PM |
|
Ryan:
I tried to change the DynamicForms_ResultsID to my preferred category name (both in creating the table and in all of my sql statements but kept having trouble. My solution was to leave the name as defined and simply use a statement in my Dynamic View which included "DynamicForms_ResultsID AS [preferred name]. This worked fine.
The tutorial #14 for the Dynamic Forms is well done until the very end. For those of us that are not familiar with stored procedures, it was difficult to replicate the binding and display components. I didn't want to use the DNN Reports module since it is not very easy to modify and instead chose to use Dynamic Views.
The other problem I ran into was that I was trying to update a table that already had 5,000+ entries and the solutions outlined did not let me enter and edit off of the same form since these previous entries were not of a Form Results Table. I ended up doing three forms (one with a Completion event to Insert, a second for Update, and a third for Delete). Not sure if this is the most efficient way to do this but it worked well for me.
Once these modules get set up (and it's not always easy), they are really very powerful. Thanks for your help.
Jim |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 12-18-2012 05:12 AM |
|
Hello Jim. It's really difficult to go over the logic and programmatic behaviors or Dynamic Forms and Dynamic Views when integrated together. Explaining the way that these features and configurations work together would become a nightmare of a response. Probably like a College Essay. These types of configuration aren't out of the box with Dynamic Views, but with a little code you can integrate your functionalities within the Dynamic Views templates. These types of items fall under our Premium Services: With Premium Services we can conduct a 1 on 1 training format to ensure that you're receiving the understanding you need of our products. http://www.datasprings.com/support/...rt-options Or we have several training sessions that are less in cost but will assist with catching you up with advanced features and configurations of Dynamic Views & Dynamic Forms. You can register for these training sessions by reviewing the page below: https://www.datasprings.com/support/training Simply click the Dynamic Views tab to see which Dynamic View training sessions are available, then you can register. Please let me know if you have any questions. Thanks, Ryan |
|
|
|
|