sum for each row in related table based on current row

[florjan]

Hi everyone! Don't even know how to name this thread. 
Anyway, I have table workorders and for each workorder there is one or multiple documents from documents table and for each document there are entries from entries table where I choose material and quantity; positive or negative.

What I want to achieve is to get a table for specific workorder in which I have all the materials from all documents for this specific workorder with calculated final quantities.

I hope you can understand what I want to achieve.

For now I got a child table in workorder where all materials for that workorder are listed:

SELECT(entries[material], IN([document],SELECT(documents[ID], AND([workorder]=[_THISROW].[ID],OR([type]="outbound",[type]="inbound")))))

Now the hard part are quantites, and I have no idea how to attach final quantity for each material in that child child table. I tried with virtual column in materials table but I don't know how to formulate a formula. It should pull values based on parent parent table.

Can anyone help with that?

[Aurelien]

Hi @florjan 

Your app structure is unclear to me. Can you please provide a drawing or screenshot of your table structure?

Thanks

About this, if your expression works:

---

@florjan wrote:

For now I got a child table in workorder where all materials for that workorder are listed:

SELECT(entries[material], IN([document],SELECT(documents[ID], AND([workorder]=[_THISROW].[ID],OR([type]="outbound",[type]="inbound")))))

---

and assuming the column name is [list_material_workorder], the related quantity would be:

[list_material_workorder][quantity]

And the sum of it would be:

SUM([list_material_workorder][quantity])

[florjan]

Thank you. Here are some screenshots.

 

 If I tell you what is workflow supposed to be. I have workorder "Aurelien's bathroom renovation". I need some materials for this workorder, so I go to that workorder and create a new document for that workorder. In that document I can add some materials let's say 10 meters of copper pipe 20 mm and 10 meters of PVC pipe 50 mm. Then at the end of the day I return 5 meters of each pipe so I create a document where I enter what came back so 5 meters of copper pipe and 5 meters of PVC pipe. Then We start to do electrical work, so I create a document where I enter 20 meters of PPJ cable, and 3 16A sockets. At the end of the day I create a document where I return 2 meters of cable and 1 socket.
When we complete all the works for this workorder I want to see for all the materials: copper pipe 20 mm, PVC pipe 50 mm, PPJ cable, 16A sockets. How many or how much of that material has been used for that workorder.

What I now realized is maybe I should add a column workorder in entries table so each entry has workorder and document assigned to it?

 

[florjan]

Looks like I made a progress. In entries I added ref column not only for document but for workorder as well and suddenly a new column appeared in workorderders table called related entries with formula REF_ROWS("entries", "workorder"). 

Some progress there.

Now there are all the entries there, but I only need unique materials with total quantity...

[Aurelien]

Hi @florjan 

I assume DOCUMENT will summarize the whole quantity of material consumed.

You are about to build something pretty complex then, as DOCUMENT will be like a twin from ENTRY.

You may need to create a complex workflow, it is all about how complex you are ready to go or not.

I suggest to try from scratch before implementing on your app.

1) You should have this list of tables and columns:

WORKORDER:

- id, type Text, initial value UNIQUEID()

MATERIAL:

the table of your materials

ENTRY:

- id, type Text, initial value UNIQUEID()

- material, type Ref, source table MATERIAL

- workorder, type Ref, source table WORKORDER

- quantity, type Decimal

DOCUMENT

- id, type Text, initial value UNIQUEID()

- material, type Ref, source table MATERIAL

- workorder, type Ref, source table WORKORDER

2) Create a virtual column "_quantity" for table DOCUMENT, with app formula:

SUM([Related ENTRY][quantity])

3) Now you need now to add to the table DOCUMENT, the materials used in each entry for the current workorder. I would suggest an action "on save".

3.a) Create a new action, based on table ENTRY, with type "add a new row to another table using values from this row". Name this action "add_material_to_document". 

Set these columns:

• material ==> [material]
• workorder ==> [workorder]

and in the section "Behavior", set this condition:

NOT(
  IN(
    [material],
    SELECT(DOCUMENT[material],
      [workorder]=[_THISROW].[workorder]
    )
  )
)

3.b) set this action on the "form save" event of the view ENTRY_Form.

I may have forgotten something, but I think it's OK. This is the most direct way to implement it I can think about. Keep us updated of your progress!