Jump to content
  • 0

Trigger Action Insert Select Distinct: Multiple Values



I aim to add unique values in a support table on which I hope to build a graphical report

On Insert into the T_EA_Entities table (which is child table linked to the main Activities table, and data is entered via a child form on the Activity Data Page) I want to insert unique Entity IDs associated with the Activity stored in a separate table called T_EA_Entities_Unique.  NB: T-EA_Entities stores nect to Entities also sometimes multiple contact persons for an entity (= multiple records)

I have attached the Trigger Action. This Action only adds one Entity ID to the T_EA_Entities_Unique table, instead of the multiple who are entered via the DataPage in the T_EA_Entities table.


Does anyone have an idea how to modify the Trigger Action so it will add multiple values to the table?


Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Hi vanderLeest,

20 hours ago, vanderLeest said:

Does anyone have an idea how to modify the Trigger Action so it will add multiple values to the table?


This can be achieved by using Joins with #inserted and table which has multiple records under same Id`s as inserted but "Select distinct" will restrict the output only to one record. 

Here is an example of the Triggered action which will insert unique records into the separate table:


Hope this helps.



Link to comment
Share on other sites

  • 0

Thanks so much for your quick reply with a nice example.

I have tried to implement it, first by selecting top 1 based on two fields, Activity_ID and Entity_ID, however that did not work.

Next I created a formula field in the tables T_Entities and T_Entities_Unique, which would result in the same concatenated ID in both tables for Activity_ID and Entity_ID combined, concatenated ID, which can have multiple values in T_Entities, but only a unique value in T_Entities_Unique.

Sadly, the following Trigger Action does not result in inserting unique values in the T_Entities_Unique table, i.e. no values are added when updating an Entity in T_Entities.

Any assistance will be sincerely appreciated as this logic (when working) will be appropriate for many use cases.


Link to comment
Share on other sites

  • 0

T_EA_Entities stores for each Activity the Entities involved and a Contact Person for each Entity. Some Entities will have more than one Contact Person, and they will be entered as a separate record. The unique composite key for the T_EA_Entities table would be a combination of Activity_ID, Entity_ID and Contact Person. Data is entered in T_EA_Entities via a child form on the Activity Data Page.

To run a combined chart and report that shows which Entities are involved with how many Activities (and links to details of the each Activity), I need a table for which the combination of Activity_ID and Entity_ID are unique, hence the name T_EA_Entities_Unique. 

On any record changes in T_EA_Entities, I want to update T_EA_Entities_Unique via a Trigger Action. To make this possible T_EA_Entities needs an Autonumber RecordNo field and a Formula Concat field combining Activity_ID and Entity_ID in a string, e.g. Right('000'+Str([@field:EAE_ID],Len([@field:EAE_ID])),6)+Right('00000'+Str([@field:Entity_ID],Len([@field:Entity_ID])),6)

The table T_EA_Entities_Unique needs the RecordNo as a Number Field and the same Formula Concat field combining Activity_ID and Entity_ID. 

The following Trigger Action will add, delete and update the Activity Entity records in T_EA_Entities_Unique, when the records in T_EA_Entities are changed.


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...