Alternate solutions to SUM(SELECT()) to keep sync times down

[weowens1984]

Looking for some help or ideas on alternate ways/ more efficient ways to do this.

So I have 4 tables.

Table A: Work Orders

Table B: Work Order Line Items (Child Of Table A)
It has a 5 columns:
1. ID
2. Description
3. Qty
4. (Virtual Column) Qty Shipped -This column uses SUM(SELECT()) On Table D to find
how many of this ID has shipped.
5. (Virtual Column) Remaining - [Qty] - [Qty Shipped]

Table C. Packing Lists

Table D. Packing Lists Line Items (Child of Table C)
It has a 4 columns:
1. ID (Using ID’s from Table B)
2. Description (Using Description from Table B)
3. Qty (This is how many are shipping on this packing list.)

This all works perfectly fine right now, but I am worried as these tables grow in row count, sync time will be significantly increased. Is there a better way to update Table B with the number that has shipped with an action or workflow?

Any help would be much appreciated.

Thanks!

[Suvrutt_Gurjar · Accepted solution]

weowens1984:

Table D. Packing Lists Line Items (Child of Table C)
It has a 4 columns:
1. ID (Using ID’s from Table B)

It is not exactly clear if table D is a child table of table B. as well. ID column of table D can reference ID( Key column) of table B, then there will an inherent reverse reference column listing child records of table D belonging to each Work Order Line Item ID. Table D of course needs to have its own different key column , that you have not mentioned.

The system created reverse reference column name in table B will be something like
[Related Packing Lists Line Items]

You could then create an expression for [Qty Shipped] in table B something like

SUM([Related Packing Lists Line Items][Qty]) Here [Qty] column is [Qty] column in table D

I believe the SUM() created by this method will be more efficient than SUM() created by a SELECT() function.
i

[weowens1984]

Thank you Suvrutt!