Custom Page Design: Update field based on another field changed

Answered

Joseph Flesche

• January 08, 2021 17:39

I am designing a page that needs to update a field (Status) based on an OnChange event for another field (Description). In other words, when the Description field is changed, set Status = NULL. I have tried the OnValidate event in DSP (Version 7.0.3.122) with a Stored Procedure, but the OnValidate event fires every time and I only want to update this field when the Description is changed. I also tried assigning a page event to the Description field, but that just makes the field clickable to allow for running identified validations and/or events. Outside of using a table trigger to monitor the update, is there any way to do this within the DSP framework?

0

• 

  Eric Weldon

  • January 08, 2021 18:03

  Hi Joe,

  If you add auditing to the table, the #AuditColumn table will tell you which field was updated (when the field is not null). You may be able to leverage that in your procedure to make the update(s). 

  0
• 

  Kurt Vandergriend

  • January 08, 2021 18:28

  It is best to avoid field names that are reserved for special use in SQL or DSP, and "Status" is one of them.  Try renaming the field.  Also, to add Auditing, go to Admin > Data Sources > Audit > Tables.  Add the table for your page, and click on "Build Audit tables."

  0
• 

  Joseph Flesche

  • January 08, 2021 18:51

  Eric Weldon, Kurt Vandergriend,

  Thank you for your replies. If I recall correctly, DSP auditing only runs based on transactions from the front end and not based on a database trigger. This caused significant performance issues for me in the past and I have implemented SQL Server triggers to audit table faster. Now it is true that I could have setup something incorrectly and thus caused more issues. Would you happen to have a link to best practices for setting up table auditing? Or maybe just on how DSP Auditing works so that I may understand to optimize the setup.

   

  Kurt Vandergriend,

  Yes, Status is a special field in SQL Server but is it also special in DSP? If so, where is it used? I have been using it for a while and have not noticed any issues so far with page rendering or functionality. 

  0
• 

  Eric Weldon

  • January 08, 2021 19:59

  Hey Joe,

  I was offering an alternative to using triggers. You are correct, the auditing record only gets inserted when a change is made via the front end (regardless if the update is made using excel integration or added/edited on the page). My understanding of how the auditing works is that the framework captures 1) the primary keys of the record and 2) what was been modified and inserts the old and new values along with the field name into the #Audit (primary keys) and #AuditColumn tables. The audit indicator of 0 means that was the old/previous value and the indicator of 1 is the new/updated value. 

  In your previous post you mentioned adding auditing caused performance issues? Or did the issues appear after you added the trigger? 

   

  0
• 

  Joseph Flesche

  • January 08, 2021 20:22

  Eric Weldon,

  The manually added SQL Server triggers work without an issue and are not noticeable from a page performance perspective.

  From my testing quite some time ago (~1 year), the DSP Auditing caused quite a bit of performance issues on the page (or really pages) that I had enabled. That is why I went down a custom trigger path in SQL Server. I'm not too sure if it was my page design in DSP, but I take advantage of the DSP header / footer page design on most (if not all) of my pages to show child data relationships. As a side note, I recently came across an issue with a page that was using an OUTER APPLY with an established sub-VIEW to gather counts of child records. When running it in SSMS, it ran in ~1 second for 10,000 records. The execution plan looked good to me. But in DSP, it would take 8-10 seconds to render the page. Removing the view used in the OUTER APPLY and placing the source SQL in place of it resolved the performance issues. I have no idea why that is the case. But it works and I am moving on keeping that in mind and that I may need to redesign a page or 2 as I go forward. (Sorry, that was long, I share just in case you pick up on something that I have done here.)

  0
• 

  Eric Weldon

  • January 08, 2021 20:41

  Joseph Flesche - I haven't experienced performance issue when adding auditing to a table - may be worth a try again. If they persist, perhaps Support can help identity any issues. Regarding general page performance, it may be more helpful to grab the querie(s) that DSP uses to render the page (using Ctrl + Shift + K). The execution plan  would most likely be different and include the joins used for combo boxes or drop down. 

  Of course if your solution is working well - no need to re-invent the wheel. :)

   

  0
• 

  Joseph Flesche

  • January 08, 2021 20:58

  Eric Weldon,

  I use CTRL+SHIFT+K quite frequently to troubleshoot pages. With my new pages, I will try the auditing functionality.

  I try not to use combo boxes or lists on the horizonal views because of the performance issues with page rendering. I mainly reserve them for verticals.

  Thank you for your feedback and suggestions.

  0

Please sign in to leave a comment.