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

[William_Stewart]

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.

Thanks

[Suvrutt_Gurjar]

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]

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

appsheet.com - EventAction - Created by Aleksi EventAction - Created by Aleksi appsheet.com

[Suvrutt_Gurjar]

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]

@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]

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]

@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]

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]

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

[William_Stewart]

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

[Suvrutt_Gurjar]

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

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

[Suvrutt_Gurjar]

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

[William_Stewart]

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

[William_Stewart]

@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]

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]

@Suvrutt_Gurjar

[William_Stewart]

@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]

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]

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

[William_Stewart]

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.

[Suvrutt_Gurjar]

Hi @William_Stewart, Thanks for update. OK.Please let me know if I can be of any assistance.

[William_Stewart]

@Suvrutt_Gurjar Thanks again, I will no doubt be needing more help! but thank you for what you have done so far

[William_Stewart]

@Suvrutt_Gurjar I am using =SELECT(Issues[Issue],[Property Name]=[_THISROW].[Property Name])

to filter the available options to those associated with Property Name. Is there a way to Select with an additional parameter? i.e. [Issue Resolved VC]=“N” so it only shows unresolved issues associated with the property name? Thanks

[Suvrutt_Gurjar]

Hi @William_Stewart, In general yes, you may wish to do so. Overall constraints expression etc working , though, will depend on how relationship between three tables that you mentioned is- property,

issues and maintenance.

[Suvrutt_Gurjar]

Hi @William_Stewart,

Thank you for your update. Regarding your update that the same issue could exist on multiple occasions,I believe it will exist multiple times in child table Maintenance? If this understanding is correct,then which status out of several “Repaired” status from child table , you will consider to update the specific issue row as “True” in parent table?

In the sample app, if you see the System view Inspection_Form, the “Event Action” setting

is set on action"Update Equipment with Inspections" action.

[William_Stewart]

Also, is there anything like the audit history to see if actions are being invoked? thanks

[William_Stewart]

@Suvrutt_Gurjar Yes- this is why I had hoped to copy the UNIQUEID key column from the Issues table to the Maintenance table and use this to find the correct row in the Issues table (i.e. [Issues].[ID]=[Maintenance].[ID Match]).

[Suvrutt_Gurjar]

Hi @William_Stewart , thank you. Is this understanding correct that Issue table is parent table and maintenance table is child table. And user updates " Repaired" status in child table maintenance.

[William_Stewart]

@Suvrutt_Gurjar Correct, so an Issue row will be created in the Issues table, then at a later date a maintenance row will be created in the Maintenance table, referencing the issue. This can have several status moments, with the final status being repaired - e.g. maintenance row could be created with status “Parts ordered” and stay on this status for a week before the row is edited and status set to “Repaired”. It is at this point that I would like to update the referenced row in the Issues table. The reason I would like to do this is to hide any issues where Issue Resolved is set to TRUE, when selecting from the list of issues in the Maintenance table.

[William_Stewart]

@Suvrutt_Gurjar I have thought about it some more, and you are right that it is unlikely there would be simultaneously the same issuedescription attached to a property name at any one time and in fact it would only be historical entries that may have identical descriptions. So the way I would like it to work is as you describe, once the issue resolved is set to true that issue cannot be reopened nor selected as a current issue when creating a maintenance row. I will describe what I have done to try and replicate the event action app you referred me to: In Maintenance table form saved, got to action “Resolve Issue” where Resolve Issue is dependent on the condition expression AND([Status]=“Repaired”,[Close Issue]=“TRUE”) and the referenced row expression is: SELECT(Issues[ID],[ID]=[_THISROW].[Issue]) and the referenced action is Close Issue: This action sets the value of the Issue Resolved column to “TRUE”

Assume I may need to change to Issues[Issue],[Issue]=[_THISROW].[Issue]??

[Suvrutt_Gurjar]

Hi @William_Stewart, Thank you for detailed update.

You mentioned that the same issue could

exist on several occasions. Could you please add , how the following scenarios are handled? An Issue, say " Roof Leaking" has been opened by reference in maintenance table and subsequently reaches in repaired status. At this point parent table row of the issue turns to TRUE. When this same issue is opened again,will the same status in the issue table turn to FALSE and again go through cycle?

Also is the same issue likely to be in “non-repaired” status simultaneously say on two rows in maintenance table? What status out of those two rows in child table will be

tracked in such cases in parent table.