Field Change, table update

Answered

Torch Locklear

• August 14, 2018 15:53

Hello,

I'm building my first custom webapp, and I'm trying to figure out how to get DSP to automatically store today's date when a given field is changed.  I'm pretty sure it will involve a business rule and a stored procedure, but I'm drawing a blank on how to proceed. Any advice and direction would be greatly appreciated!

The field in question is named QueryStatus and is on the tbLog_PrimaryData table.  When this is changed, I want it to store the new status and the date on the tbLog_QueryStatusDates table.

Thanks,

Torch

1

• Best answer

  

  Jon Green

  • August 14, 2018 16:04

  You have a few options here that you could use to capture this information. 

  1. Within the DSP framework we have auditing capability that can be switch on for any page and that would capture any changes made to data in a page and record this to an audit table, which is not exactly what you are asking for but would capture this information and more.

  2. You can create and register a Stored Procedure as a business rule that would be assigned to the onValidate event for the page.  Within the stored procedure you could compare the value in the source table to that in the target and if missing insert the record or if existing but changed then update the value and date of the change.

  3. If you just want to capture the change to one or more specific columns, you have the option to just put a trigger on the table within SQL Server so that the on insert / on update events are captured and then written to the specific target table you want to write the entry to.  This is not specifically within the DSP framework but another option used when developing apps in the DSP.

  0
• 

  Kurt Vandergriend

  • August 14, 2018 16:11

  Torch:

  If you want to track when the latest change is made to ANY field, you can use standard DSP, which will record this in the ChangedOn field.  Also, all custom webapp tables should contain these additional columns to support auditing, locking, and record status: boaStatus INT, AddedBy nvarchar(50), AddedOn smalldatetime, AddedVia nvarchar(50), ChangedBy nvarchar(50), ChangedOn smalldatetime, ChangedVia nvarchar(50), LockedBy nvarchar(50), LockedBy nvarchar(50), LockedOn smalldatetime. These columns can easily be added to all tables by using the Append Columns toolbar button on the Vertical view of the Data Source in Admin > Data Sources.

  In addition, you can turn on auditing for any table, which will track all changes.  This is done in Admin > Datasource > Audit > Tables.  Go to the online help in DSP (click on ?), and search for "Enable Auditing" to get details of how to proceed from there.

  1
• 

  Torch Locklear

  • August 14, 2018 17:20

  Hi Kurt,

  Yes, I have appended all of the default fields to all tables.  The problem with using those for this purpose is that I would wind up with multiple rows for each record on the primary table, whereas what I am trying to do is to have a single change table just for the statuses.  

  Thank you though!

  0
• 

  Torch Locklear

  • August 14, 2018 17:21

  Thanks, Jon.  I'll look at putting together the SQL for option 2 as it looks like my best option.

  0

Please sign in to leave a comment.