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

0 24 615
24 REPLIES 24

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.

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.

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

Hi @AntonyCole1979,

Good to know that. I believe it willwork with even realcolumn. For it to get working with real column , you will need to “open” or"edit" all existing parent table records and resave thos. This is so becausewith real columns the changes in expressions take effect after opening and closing the recprd again.

Hi @AntonyCole1979,

Good to know that. Ibelieve it willwork with even realcolumn. For it to get working with real column , you will need to “open” or"edit" all existing parent table records and resave thos. This is so because with real columns the changes in expressions take effect after opening and closing the recprd again.

I wanted to ensure that at least basic expression is working, so requested you to verify with a VC.

Thanks form your help Suvrutt, looks like it is doing what I expected it to do after some testing. I will now do what you have said to get the real column working. Appreciate your help, Antony

You are welcome. Pleasedo test well for all the required conditions. So just to summarize, I believe the problem essentially was , the expression was working but you were trying to check the expression with real column in existing records without opening each record. Of course, it was my bad and fault to miss to suggest you to check by opening and closing existing records.

Incidentally after you mentioned facing issues, I had worked out another expression that also you may keep handy. It is a bit lengthier than earlier one. I have tested it in another test app.

IF(OR(CONTAINS([Assigned To][Job Status], “Started”), CONTAINS([Assigned To][Job Status], “Assigned”), CONTAINS([Assigned To][Job Status], “On Hold”)), “WIP”,“Completed”)

Thanks Suvrutt, I will try this one out also

Top Labels in this Space