Last chosen status slice

Adm_Tech · 02-15-2020 01:56 AM

Hello Community! Help needed.

I’ve been trying to create a slice to show only jobs with actual (last) status “STOPPED”.

The table has inspections done everyday on products and status for that product after inspection. The target is to show always - only those which are STOPPED (last visit finished with status STOPPED).

I’ve tried expressions with MAXROW() etc. but none of them works, I’ve studied similar cases on community hints but couldn’t find the answer. I’ve stucked.

If you have any idea how to build that expression for the slice - I’ll appreciate sharing with me!

Suvrutt_Gurjar

Hi @Admin_Techlift,

If I have understood your requirement , it sounds that you are looking to include in slice for those job ids for each serial number where the "Stopped"status is latest.

If so, you may try the expression something like below. Please note that the expression is not efficient and it could impact the performance , especially after the table data grows more. The expression has two SELECT() statements. There may be a better way to achieve it.

IN([Job ID], SELECT(Table Name[Job ID],AND([Serial Number]=[_THISROW].[Serial Number], IN([Date],LIST(MAX(SELECT(Table Name[Date], AND([Serial Number]=[_THISROW].[Serial Number], [Status]=“Stopped”))))))))

Adm_Tech

Thank you! I’ll try this!

I’m thinking to build it another way: to add the last product status to a product table (with the workflow/action: change data in another table). This way - I could have an actual status for all products in the database, and after - I would make the slice for that table, instead of Jobs table…

The only problem for me is how build that expression copying actual status from a job row to the products table. Truly speaking I still have some problems with building similar expressions in that kind of workflow/ actions (bad student)…

Suvrutt_Gurjar

Hi @Admin_Techlift,

You are welcome. In general your idea sounds good because it appears to split the processing at two places. Of course , this is just a general comment based on overview you mentioned.

The community members will definitely share their inputs if you need any further inputs. Please do post with more details. All the best !

Adm_Tech

Thank you!

PS. Spending saturday in the office with pending improvements in my appsheet apps, your help has made my day far better and sunny

MultiTech

I would advocate for a different way of accomplishing this formula’s result - anytime you have a SELECT() inside another SELECT() you should rethink how you’re going about things because performance will be affected on an exponential curve based on the number of records in the sheets.

I don’t have any advice for how I would do this differently, it all depends on how things are put together in your app, I just want to voice caution with formulas that have nested SELECT() expressions.

Suvrutt_Gurjar

Thank you @MultiTech_Visions. I completely agree with you . I also cautioned on inefficienty part and requested @Admin_Techlift to post his current app configuration so that one can suggest a better way of splitting the processing.

As you righly mentioned, SELECT() within SELECT() can become a challenge of logarithmic proportion with each additional record.

Adm_Tech

Thank you.

Product table is referenced by serial number to Jobs table. So - with each new Job entry I would copy the actual status (with a workflow + action) to the Product table (dedicated column).

Workflow form Jobs Table - triggers when new entry comes
Action - chooses the Job entry product in Product Table and sets up the value of LAST STATUS for… (?)

Question mark because I don’t know how to build such expression in the action definition (set the values of some columns in this row).
The only visible columns (In action, expression builder) are those from Products table, without any connection to Jobs tablewith this entry row and Job STATUS

MultiTech

If you’re building an action on the products table, you can de-reference the job status from the Jobs reference column

[Job_Link].[Job_Status]

Adm_Tech

Yes. But 1 product from Product table has many jobs in Jobs Table. With possible different statuses.

In that case the trigger (workflow) fires when actual job is opened (just finished) and only that status should be migrated to the product table.

Maybe I should use then MAXROW and _ROWNUMBER to identify the status value form just created row…

Rentals_S

I have tried to solv this problem for 6month. Looking forward to seeing your solution.
Regards
Micki