sum for each row in related table based on current row

AppSheet AppSheet Q&A
1

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?

2

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:

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])
1 Like
3

Thank you. Here are some screenshots.

table documents.png
table documents.png1128×663 34.4 KB

table workorders.png
table workorders.png1119×721 44 KB

table entries.png
table entries.png1144×785 42.7 KB

table materials.png
table materials.png1141×779 44.5 KB

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?

4

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…

5

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

  1. Create a virtual column “_quantity” for table DOCUMENT, with app formula:
SUM([Related ENTRY][quantity])
  1. 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.

Aurelien_0-1728385911517.png
Aurelien_0-1728385911517.png879×304 20.8 KB

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!

1 Like