Summarize two column list

[Dimitrios]

Hello,

If I have two lists :

ListA 

Apples 20

Oranges 15

Pears 120

ListB

Apples 13

Oranges 30

Nectarines 70

 

... how can I end up to 

Apples 33

Oranges 45

Pears 120

Nectarines 70,

with a custom function?

 

Thank you!

 

[comment]

1 hour ago, Dimitrios said:

I have two lists

I am not sure what exactly you mean by "list" and where exactly you have these lists. Certainly, the correct place to store data like that is in records and fields  - a separate record for each item in your "list", and a separate field for each fact describing an item (Product, Quantity). Then you have all the built-in tools for summarizing data at your disposal - for example, sort by Product and show only the sub-summary values -  and you don't have to try and build your own database application using custom functions.

 

[Dimitrios]

Well, I would like to clarify the situation.

a) I am on a layout based on DOCUMENTS table. In this layout I have a portal to show DOCUMENT LINES. 

I populate the lines with Item/Lot/Serial/Qty of items I want to operate. (receive,write off, lent, import to warehouse,or export)

b )  Based on the document/operation type and the entered document lines, I perform some updates to my ITEM SLOTS table (i.e. archive obsolete records and create new with updated qty)

c) My script performs these updates transactional-wise. (no commits until finished)

d) Sometimes -if you want I can clarify- , during a transaction I need to merge the DocumentLines of two Documents. I could do that with Mikhail Edoshin's FS technique but I dont want to leave my layout .

e) I have these lists in global variables

 

Edited March 31, 2017 by Dimitrios

[Dimitrios]

I was thinking that something could be accomplished with CustomList custom function but I can't figure it out

[comment]

5 hours ago, Dimitrios said:

I need to merge the DocumentLines of two Documents. I could do that with Mikhail Edoshin's FS technique

I believe that would be the smart thing to do.

5 hours ago, Dimitrios said:

but I dont want to leave my layout .

Why not? If you want to keep your found set and/or sort order, you can open a new window, do the summary, and close the window to return to your original state.

[Dimitrios]

Well, your suggestion makes sense to me.

Opening a new window and going to a different layount won't cause a "Commit" right?

[comment]

1 hour ago, Dimitrios said:

Opening a new window and going to a different layount won't cause a "Commit" right?

Right: a record can be committed only in the same window in which it was opened.

[Dimitrios]

Thank you! Is there any known ways to -instead of adding quantities - subtract the qty of certain records? I guess that would require to have a calculation field to make the qty of the records I want to subtract, negative. Is there another option?

[comment]

4 hours ago, Dimitrios said:

Is there any known ways to -instead of adding quantities - subtract the qty of certain records?

It depends on how you identify those "certain records". Is there a field that you can sort by?

[Dimitrios]

These records are from the same table of course (DOCUMENT LINES) and they all have positive quantities. I can sort by any field from the table

[comment]

I am afraid that doesn't answer my question. Perhaps you should expand your original example and include some of those records that need to be subtracted, along with the field that makes them different from the other records (that need to be added).

[Dimitrios]

I am sorry for the late reply....

Inventory distribution

All changes in inventory happen via creation of a new OPERATION/DOCUMENT record.

OPERATIONS---<TRANSACTIONS

Each Operation/Document holds a number or TRANSACTIONS. Each Line refers to a unique combination of the fields: Item/Lot/Serial/ExpirationDate/Condition. The quantity may vary.

When the document is saved, a script updates the records in ITEM SLOTS table. This table is a "map" of the real-world items.

Looping through each TRANSACTION, the script either creates new ITEM SLOTS or changes the quantity of ITEM SLOTS records and other attributes.

 

Problem: 

Some OPERATION/DOCUMENTS are printed with their TRANSACTIONS (these documents model the operation that has been performed)

Some other OPERATION/DOCUMENTS are more like a "Receipt" and their Lines must show the result of more than one OPERATION.

                  Examples:  

• Department A issues an OPERATION/DOC to Mike (100 Widget X) - so far the TRANSACTIONS match the Lines of the DOCUMENT
• Department A issues another OPERATION/DOC to Mike (100 Widget X, 100 Widget Y). - the printed document must show 200 Widget X, 100 Widget Y, If I decide to merge the two operations. I might as well keep them separated, in which case we fall to the first example.
• Department A issues another OPERATION/DOC to receive back from Mike. (20 Widget X) - In this case I open an existing OPERATION and the Items received back are subtracted.

                 My Solution:                                 

OPERATIONS/DOCUMENTS---<TRANSACTIONS

OPERATIONS/DOCUMENTS---<DOCUMENT LINES

               So if I want to issue a separate receipt, I just copy the TRANSACTIONS to DOCUMENT LINES or If I want to merge, I merge the TRANSACTIONS of the NEW OPERATION/DOC with the DOCUMENT LINES of the old OPERATION/DOC.

               Documents are always printed with their DOCUMENT LINES. 

 

Thanks.

 

 

 

Edited April 21, 2017 by Dimitrios