Jump to content
Sign in to follow this  
Dimitrios

Summarize two column list

Recommended Posts

Dimitrios    0

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!

 

Share this post


Link to post
Share on other sites
comment    1,370
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.

 

Share this post


Link to post
Share on other sites
Dimitrios    0
Posted (edited)

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 by Dimitrios

Share this post


Link to post
Share on other sites
Dimitrios    0

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

Share this post


Link to post
Share on other sites
comment    1,370
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.

Share this post


Link to post
Share on other sites
Dimitrios    0

Well, your suggestion makes sense to me.

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

Share this post


Link to post
Share on other sites
comment    1,370
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.

Share this post


Link to post
Share on other sites
Dimitrios    0

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?

Share this post


Link to post
Share on other sites
comment    1,370
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?

Share this post


Link to post
Share on other sites
Dimitrios    0

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

Share this post


Link to post
Share on other sites
comment    1,370

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).

Share this post


Link to post
Share on other sites
Dimitrios    0
Posted (edited)

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 by Dimitrios

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Similar Content

    • By jbante
      FileMaker 16 introduced a collection of built-in functions for manipulating data serialized as JSON. This makes it easier for FileMaker applications to interact with many web services. This will also make JSON the de facto standard format for scripts within FileMaker to pass parameters and results to each other, improving code sharing within the FileMaker community.
      JSON does not have a broad palette of scalar data types to choose from: text, number, boolean, and null. Even with those, FileMaker's JSONGetElement function always returns a text result, even when the serialized JSON value is a number or boolean. So I made a handful of custom functions and scripts for sending and receiving typed data with JSON.
      The module is hosted on GitHub, or you can download it directly.
    • By macjos


      View File read json custom function without plugin, FileMaker only
      Being unsatisfied with solutions I found on the internet and wanting a solution without plugin I created a set of custom functions that can read a path out of a json-string.
       
      Syntax:
       
      json_path ( json-string ; path )
       
       
      examples:
       
      json_path( $user_prefs ; "Privileges/Modules" )
      json_path( $json_data ; "Menus/Menu[10]/MenuItem[5]/Label" )
       
      Submitter macjos Submitted 09/30/2016 Category Samples FM Version FM Version: 14  
    • By macjos
      Being unsatisfied with solutions I found on the internet and wanting a solution without plugin I created a set of custom functions that can read a path out of a json-string.
       
      Syntax:
       
      json_path ( json-string ; path )
       
       
      examples:
       
      json_path( $user_prefs ; "Privileges/Modules" )
      json_path( $json_data ; "Menus/Menu[10]/MenuItem[5]/Label" )
       
    • By Anthony
      I am using a custom function to format a number into currency format ( two decimals ). 
      RoundDecimals(11.0) = 11.00 The problems occurs when I use a variable:
      $amount = 11.0 RoundDecimals($amount) = 11.11 Similarly, I get the same error using a string:
      RoundDecimals("11.0") = 11.11  
      Is there any tricks to get around this issue ? 
      This is the custom function that I used (I replaced the comma for a dot and defaulted second parameter to 2):
      https://www.briandunning.com/cf/974
       
    • By shevyshevy
      I am having difficulty removing the .JPG extension from the file name of my imported pictures. The Pictures are actually students pictures saved with the student's ID Numbers.
      Example SLT-098-00034.jpg  ,  SLT-088-11123.jpg
      I am trying to extract the real ID Numbers from the File names which should be in this format  SLT/098/33322
      Accomplishment So Far..
      Using the Substitute function,  Substitute (text; searchstring; replacestring)  , I have been able to convert the file name from SLT-098-00034.jpg  to SLT/098/00034.jpg
      Now, I want to remove the .jpg extension, but can't achieve that using the Substitute function..  Please is there a better way to achieve this?
×

Important Information

By using this site, you agree to our Terms of Use.