This article describes an issue that occurs when a table trigger is fired by the editing of a child table that has been opened from a screen that is not the parent.
Description of the problem:
1. A table trigger is configured to fire when a child table of People (for example, but this can be any other table) record is modified (i.e. Application Detail)
2. The trigger is configured to use information in (DB-readers or mailers) containing details taken from the Foreign Key Record (People Table).
3. When the workflow is triggered from the screen with data grid not based on parent table, the Foreign key is taken from the table which is a base for the screen, not the original key stored in the table.
How To Recreate
1. Create a Vacancy Table. This table should be defined as a top level table.
2. Create an Application History Table as a child of the People table. This table must have a lookup to the Vacancy Table.
3. Create simple screens that allows you to create a vacancy and application history record.
4. Create another screen as follows:
1st Dataset Parent Table = Vacancy
2nd Dataset Table = Application History
5. Join Dataset 1 to 2 via the Vacancy.Vacancy_ID = Application History.Vacancy Lookup
6. Add Dataset 2 information to the Vacancy screen via a datagrid that opens the simple application history screen you created in step 3.
7. Create a workflow table trigger on the Application History table that sends a message to the ANADMIN account and contains data from the
FOREIGN KEY PEOPLE record (i.e. Full Name, FirstName, Date of Birth)
8. Create the actual trigger so that it fires whenever any change is made on the Application History table.
9. Login to the Web App and using the screen and datagrid created in 4 navigate through to an Application History record, modify some data and hit
save.
10. The expected FK – PEOPLE fields will be blank in the message.
This is a known to Vizual issue that has been raised with development for resolution in a future release.
Workaround from Vizual:
The following workaround is available (Please see symptoms of issue as this resolution relates to that information):
1. Create a new field on the Application History table called Related People Record. This should be a lookup to the People table.
2. Populate the Related People Record field with the Foreign Key data when the record is initially created.
3. The message can be created using this lookup as opposed to using the Foreign Key information.
This is quite hefty solution. Some better ideas came to my mind.
Better Workaround:
You need to amend the triggered workflow – in the begining of the flow, just add another Database Reader that takes Foreign Key from the table, and in the following stages refer to this reader with foreign key, not the one you can take directly from Input Table.
This way you don’t need to change the table structure and multyply table fileds, having duplicative information stored in the same table, which can lead to the further bugs.