Good morning. I have 2 tables "Issues" and "...

(William Stewart) #1

Good morning.

I have 2 tables “Issues” and “Maintenance” Issues table has a column called “ID” with UNIQUEID(), this is the key column , and column called “Issue”.

Within the Maintenance table I have a column called “Issue” with a reference to the parent table [Issues].[Issue] to allow me to select from

a filtered list depending on which property name I select. What I would like to achieve is, when creating a row in the Maintenance table, I would like to edit the parent row / Issue from the Issues table i.e. once I select an Issue in the Maintenance table and say it is “Repaired” I would like to edit the appropriate Issue row to set the column “Issue Resolved” to TRUE.

I had thought I could use a webhook / API to achieve this however in order to point it at the right row I need to have the Key ID for my JSON body (I think). So, is there a better way I could do this, or if I am on the right track is there a way to initialise a column with the value from a Key Column in another table? I have tried to use the app formula within column “ID Match” in the Maintenance table [Issue].[ID] and whilst this expression is valid it does not populate the cell with the UNIQUEID() from the referenced Issues table.


(Suvrutt Gurjar) #2

Hi @William_Stewart, If my understanding of your requirement is correct, you wish to update the parent table’s issue status column to “true” if the child table’s issue status field is set to “resolved”. Also is there one to one relation between your child table and parent table , meaning one issue in issue table corresponds to one issue in maintenance table for a particular property?

If it is so in that case, I believe you can use the concept of event actions to do the needful.

(Suvrutt Gurjar) #3

There is a sample app created by senior colleague @Aleksi_Alkio , wherein an update in child table updates parent table as well. - EventAction - Created by Aleksi EventAction - Created by Aleksi

(Suvrutt Gurjar) #4

Hi @William_Stewart,

Thank you.

  1. Based on my understanding of your requirement, if you are fine with a virtual column to capture the status of the Issue from maintenance table into Issue table, you may create the VC , called say [Repair Status V] in parent table with an expression like

=IN(“Repaired”,[Related Maintenances][Repair Status Tracking Column Name in Maintenance Table])

[Related Maintenances]

is reverse ref list column in Issue table. As soon as the Issue status goes through cycles and goes to “Repaired” status in maintenance table, this VC will turn to TRUE

2)If you need the issue status to be available in parent table in real column, you can go ahead with Event Action approach. I have tested that as well to be working. We could discuss implementation if need be.

(William Stewart) #5

@Suvrutt_Gurjar I have implemented your suggestion with expression in Issue Resolved VC: =IN(“Repaired”,[Related Maintenances][Status])

This does not appear to be giving the desired result. I should have mentioned that the Status column in the Maintenance table is an Enum column, unsure if this is relevant.

(Suvrutt Gurjar) #6

Hi @William_Stewart, Thank you for update. May I know , what result you are getting ? I tested it at my end and it works. Presume your VC in parent table containing this expression is of column type

YES/NO of column,since it needs to give TRUE/FALSE results.

(William Stewart) #7

@Suvrutt_Gurjar correct. I am using a Yes/No column, this was automatic from my expression. The result simply stays as “N” in the display.

(Suvrutt Gurjar) #8

Hi @William_Stewart, Thank you for the update. Is the result so, even when you change the status to"Repaired" in maintenance table status column?

(William Stewart) #9

@Suvrutt_Gurjar Yes - it remains as N. Is the update of the VC automatic upon Sync?

(William Stewart) #10

@Suvrutt_Gurjar I do have the [Related Maintenances] column hidden within the Issues table, but can’t imagine this is a problem?

(Suvrutt Gurjar) #11

Hi @William_Stewart,Yes that should not be a problem.

Hope the [Related Maintenances] is a list type column.

(Suvrutt Gurjar) #12

What the “Test” panel of the column is showing?

(William Stewart) #13

@Suvrutt_Gurjar yes related maintenance is List type. I will check Test after work. Thanks for all your time.

(William Stewart) #14

@Suvrutt_Gurjar Thank you for your reply. I should have mentioned that whilst each Issue has a unique ID, the referenced column is for the Issue column from Issues table and the same Issue could exist on multiple occasions. I have tried to copy the EventAction by Aleksi. I haven’t managed to get it to change the parent copy, I will continue to try however. Can you tell me, what triggers the event? - just the act of saving the entry?

(Suvrutt Gurjar) #15

Hi @William_Stewart,One suggestion

is (if not done earlier) ,please save the record after changing the status to "Repaired"in maintenance table and please check thereafter in issues table.

(William Stewart) #16


(William Stewart) #17

@Suvrutt_Gurjar I unhid the Related Maintenances column to check what was being shown there, and there aren’t any entries! which will explain why the VC isn’t working. I have obviously mucked up my reference somehow, so I will try to figure that out.

(William Stewart) #18

OK, so my issue was that[Issue] was not the Key column of the issues table, so whilst it referenced it, it was not creating a relationship. Just to clarify, I didn’t make it the key column as I could have: Property 1 Roof Leaking, Property 2 Roof leaking where “Roof Leaking” is the Issue or key. Is this a problem??

(Suvrutt Gurjar) #19

Hi @William_Stewart, Thank you for update. Could you please add which table, the property columns are?

(William Stewart) #20

The property column also exists in the Issues table, however that in itself

references [Property Name] within the Property table. Unfortunately, by making the Issue column the key column, my validif constraint is no longer working… Which means I can’t actually select the issue from the Maintenance table in order to create the relationship.