Help with maxrow in child list

Hi, I have 2 tables “Jobs” (Parent), and “Allocated Jobs” (Child). They are linked via the [Job Ref] column which is the key in the “Jobs” table. I need a way to update the [Job Status] column in the “Jobs” table to the [Job Status]=“Completed” when all rows for the current record show completed in the “Allocated Jobs” table. I hope that makes some sense!!?
Thanks in advance

Antony

If I have understood your requirement correctly, you may wish to try an expression something like below in the [Job Status] column of the parent.

The approach uses indirect verification approach.

IFS( NOT(IN( “WIP”, [Related Allocated Jobs][Jobs Status AJ])), “Completed”)

Here "WIP"is the other status of the job ( say Work In progress) when it is not completed.

[Related Allocated Jobs] is the reverse reference column in the parent table corresponding to the allocated jobs child table.

[Jobs Status AJ] is the status column in the child table.

If you have more statuses, I believe you may need to build more OR statements.

1 Like

Thanks Suvrutt,
Sorry should have explained further. It’s close but there are 4 status in the child record “Assigned”, “Started”, “On Hold”, “Completed”. In the parent column [Job Status] I only need to show when it’s “Completed”, and blank when there is no child records.

Thanks

Antony

Hi @AntonyCole1979,

Thank you. Does an expression something like below help ?

IF(AND(COUNT([Related Allocated Jobs][Jobs Status AJ])>0, COUNT(INTERSECT([Related Allocated Jobs][Jobs Status AJ], LIST(“Started”,“Assigned”, “On Hold”)))=0), “Completed”,"")

I have not tested it,so there coul be some parenthesis missing. Also the INTERSECT() expression may possibly give error.

2 Likes

Thanks Suvrutt,

Syntax is correct and not throwing any errors, so I will get to testing. Thanks for your help!

Antony

Sure, thank you. Please extensively test as per your requirements and let us know how it goes.

Hi Suvrutt,

Unfortunately I cannot even get the column to show on screen so I assume is blank and the below doesn’t work?
IF(AND(COUNT([Assigned To][Job Status])>0, COUNT(INTERSECT([Assigned To][Job Status], LIST(“Started”,“Assigned”, “On Hold”)))=0), “Completed”,"")

Thank you Antony , is it possible that you can share test results screen of the editor?

Do you want me to print screen, or do you want access to the app?

Hi @AntonyCole1979,

Can you please try the following expression once?

IF(AND(COUNT([Assigned To][Job Status])>0, COUNT(INTERSECT([Assigned To][Job Status], LIST(“Started”,“Assigned”, “On Hold”)))=0), “Completed”,“WIP”)

and let me know what you see in the parent table?

It still shows nothing. when I test the expression the result says “Completed”. But nothing shows in the [Job Status] Column? Column is set to text, and formula entered in the formula box?

Thank you. Is it possible that you can share a screenshot of the test pageof the editor and the column settings of the parent table?

Thank you. Are you able to expand the test expression in theabove screen, by tapping the icon below “completed”

Thank you. Is it possible that you use slighly different name for the [Job Status] column in the child table , say [Job StatusAJ] and use that in the above expression and test it ?

Also can you please make the [Job Status] column in the parent a VC and test?

Can I not change the [Job status] in the parent instead of the child, as the child has some actions attached to it?

Yes, sure Antony.

Looks like this has worked with VC column