Unanswered

need to be able to select date from a subtable based around one row data

(0) Share

Report

Posted on by troyhoskison

Hi,

I have a works order table (Dataverse), and a work order routing table, The work order routing table list all the stages of production, after each stage is completed the put a check in the completed check box. Each routing has a sequential (but not Consecutive numbers, e.g. 10,20,25,30) number so you know what order they go in.

There is two things I want to be able to do:-

1) in the work order table have a field that displays the I would like to know the production resource (field on work order routing table) for the lowest numbered routing that isn't completed

2) When a work routing stage is complete then next recorded get highlighted as the active record (yes/no check box)

The information need to update in real time

I have tried a few ideas but I soon got nowhere fast.

Any thoughts?

Troy

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (0)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @troyhoskison,

I assume you're using model-driven apps/forms?

Not sure I understand exactly what you need based on the sentence, but I think it's this: "the next available routing number (lowest) displayed on the "Work Order" form. If so, you can achieve with a view and a subgrid on the Work Order form.

Create a create that filters only available (not completed), pick desired columns and order by number ascending.

For the subgrid properties, do not select "only related records", but "all row types" and pick the routing table as an entity/table. Pick the view and you can configure the subgrid to only show 1 row/record. If you absolutely need to show only one row in that view/subgrid, then there's more advance ways to do it by editing the FetchXml.

Where (which entity/table) do you want the record to be highlighted? Form, list/view, etc? When a routing stage record is completed automatically, the row will be filtered out from 1) above.

Hope this helps!

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @troyhoskison,

This is actually similar to what I had to do on my last project. I was using tasks that needed to be completed in order with a predecessor and successor to each task. When the predecessor was completed it needed to move the subsequent task into a "Not Started" status. We also had different types of entities that required different tasks in that needed to be completed.

So... I would do something like the following (not sure if it would make sense for you though)

1. Create a couple entities with the parent being the Work Order Type and the Child (1:N) being the WIP steps of that work order that would need to be completed. You could then relate these Work Order Types to Products (or maybe the product is the parent if it is distinct based on the product.

As Example you could have Products 1, 2, 3 all use a process that has:
Process A
10. Do Something
20. Do something else
35. Yup, do something else
and these would all have Process A as a field on the product.

Each Work Order Step would have two lookups for the Predecessor and Successor Work Order Steps. The first would have no Predecessor and the last will have no Successor.

Now to do some of the things you were looking to do with this.
1. When you instantiate a Work Order you would have Power Automate flow kickoff that would create the Work Order Steps that are tied to the type of work Order and relate them to the Work Order so you would then have your WIP process. When the flow creates them it would set the predecessor and successor to each appropriately. The last step in the flow would be to Activate the first WIP step (i.e. 10 above).
2. When someone complete 10 then you have a realtime workflow that kicks off on the status change and will then do an Update to the related (successor) WIP item and activate appropriately. The last step in this real time workflow would also set the Work Order next step to the successor's step number.

This worked well for me in my last project. Everything would be realtime except for the initial flow to create the WIP record steps for the work order.

Hope this helps! Please accept if answers your question or Like if helped in some way.

Thanks,

Drew

Was this reply helpful? Yes No
troyhoskison 59 on at

Like (0)

Report
Copy link

Link copied!

I should have mentioned that I am using a model based app.

My objective to two fold, Firstly I need to be able to list all works orders and see which machine they are currently at. I also need to be able to filter the list to show all works order at a particular machine.

If a create a view on the routing table I can see all the works order that are going to be going through that machine, and I and use the calculated field to pull the information I need from the header record. But I need to be able to filter the list so I only see the routing of where it is. So my thoughts are I need to be able to have a flag on the record to show that it is the active or a need a more advanced filter, where I can do a group by lowest value.

Troy

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @troyhoskison,

The approach I detailed out would accomplish this since it would activate the successor work order sequence after the prior one is completed. This would then identify it as the active and you could view the active ones easily. The ones further in the sequence (to happen later) could be in a "Waiting" status or whatever to reflect that they are not ready.

Please review my steps and let me know if you have a question or concern on the approach.

Thanks,

Drew

Was this reply helpful? Yes No
troyhoskison 59 on at

Like (0)

Report
Copy link

Link copied!

unless I am mistaken the solution you propose requires a work flow for every product. We have hundreds of products, we also want the flexibility to be able change a work flow, I.e. need to add a rework step.

Annoyingly this is really easy solve in SQL or even FetchXML, but I can't find a way to base a view on FetchXML?

troy

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @troyhoskison,

It requires initial setup of the WIP process required and if each product has a different WIP process then yes but otherwise it would not. You would define the WIP process that would be utilized for groups of products and then assign the process to the product. When a work order is created for the product then you would lookup the Process that is related and the Power Automate flow would generate all the steps for that Work Order dynamically. You would have the ability to change the definition of these processes over time because they are tables that you can change the steps etc.
If this is not what you are looking for then no worries. Just wanted to provide more details on the approach.

Thanks,

Drew

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @troyhoskison,

Thanks for clarifying. Your requirements seems simply and suggest to keep the solution simple as well.

Able to list all works orders and see which machine they are currently:Create a view on Routing table. Filter where completed is false, order by sequential num ascending. You will see the work order in more than once in the list but the first appearance is where they are currently. Does this suffice? Super easy to implement and let the users know to look for the 1st row.
Note: assume you have the Routing table on the app and sitemap.

filter the list to show all works order at a particular machine:
Create another view on Routing Table but not filter, therefore listing all the machines. You can use out-of-the-box view filters to filter on demand.

If you absolutely need to group by lowest, then with FetchXml you can try something like this and save the FetchXml to a view that you can then reference in the app. A tool to help with this is: https://www.xrmtoolbox.com/plugins/Cinteros.XrmToolBox.ViewDesigner/

<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='routingtable'> <attribute name='workorder' alias='workorder' groupby='true' /> <attribute name='sequencenumber' alias='sequencenumber' aggregate='max'/> <filter type='and'> <condition attribute='completed' operator='eq' value='1' /> </filter> </entity> </fetch>

Hope this helps!

Was this reply helpful? Yes No