Jump to content
Sign in to follow this  

Summarize two column list

Recommended Posts


If I have two lists :


Apples 20

Oranges 15

Pears 120


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

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

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

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

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

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

I am sorry for the late reply....

Inventory distribution

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


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.



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.


  • 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:                                 



               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. 






Edited by Dimitrios

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Richard Carlton
      For this Coaches’ Corner tip, Kristian Olsen demos a simple technique for creating a popup menu in which you can make multiple selections. Very useful!
      Grab the sample file here:
      FileMaker Coaches' Corner - Tip 4 - Editable Popup Menu - Custom Function
      Most Recent Upload https://goo.gl/Dbn9fm
      Get up to speed with the FileMaker Pro 16 Video Training Course!
      Top Rated Course by FileMaker Expert, Richard Carlton.
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 26 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference, Richard will teach you all the ins and outs of building FileMaker Solutions.
      The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide.
      Richard works closely with RCC's staff: a team of 28 FileMaker developers and supporting web designers.
      He has offices in California, Nevada, and Texas.
      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving FileMaker for Startups and Entrepreneurs, and client-server integration.
      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.
      RCC, Filemaker Videos, and LearningFileMaker.com are headquartered in Santa Clara, CA.
      Please feel free to contact us at support@rcconsulting.com
      FileMaker Pro is simply a powerful software used to create custom apps that work seamlessly across iPad, iPhone, Windows, Mac, and the web
      Transform your business with the FileMaker Platform
      Free FileMaker Training Videos Channel https://www.youtube.com/user/FileMakerVideos
      50 Hour FileMaker Pro 16 Video Training Course-FileMaker 16 News-Online FileMaker 16 Training Videos
      Playlist https://www.youtube.com/watch?v=KpQqLLDcZ8I&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_&index=13
      Top 10 New Features in FileMaker 16-FileMaker 16 News-FileMaker 16 Instructional Videos-FileMaker 16
      Playlist https://www.youtube.com/watch?v=urh8iHOCxkg&t=130s&index=1&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_
      Sharing your Database with Other Devices and Users-FileMaker 16 News-FileMaker 16 Database Sharing
      Playlist https://www.youtube.com/watch?v=GF82vkYtCtA&index=8&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_
      Introduction to FileMaker WebDirect 16-FileMaker 16 News-Online FileMaker 16 Training Videos
      Playlist https://www.youtube.com/watch?v=uaZKIpBjMAM&list=PLjTvUZtwtgBSVV1-4pFG4SHAhCIP3Yy-I&index=17&t=10s
      A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data
      Official site provides the SDK, Developer's Guide, Reference, and Android Market for the open source project
      Free FileMaker videos check out ...http://www.filemakervideos.com
      Download the FileMaker Pro 16 & FileMaker GO 16 for mobile devices training videos at http://www.learningfilemaker.com
      Download FileMaker Go 16 video training at http://learningfilemaker.com/FMGO-16/fmgo16.php
      Download FileMaker 16 Full Video Training Bundle at http://learningfilemaker.com/subscription.php
      FileMaker Video Training Review-FileMaker 16 Video Course Review-FileMaker Pro 16 101 Course Review
      Playlist https://www.youtube.com/watch?v=mF6Uor0KmKo&list=PLjTvUZtwtgBT8tNHuzF6cOKC_37zCTQl6&index=18
      Use FileMaker to create an app with the FileMaker Training Series
      FileMaker Pro is a cross-platform relational database application from FileMaker Inc.
      Comment, Like & Share Our Videos.
      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media
    • By NewBoard
      I am attempting to use the following script in a Database:
      Set Variable [$supplierCount; Value: ValueCount ( List ( Suppliers::UUID ) )] Set Variable [$month; Value: MonthName ( Get ( CurrentDate ) )] Set Variable [$suppliers; Value: List ( Suppliers::UUID )] Loop Set Variable [$i; Value: $i+1] Exit Loop If [$i > $supplierCount] New Record/Request Set Field [Month::Month; $month] Set Field [Month::fk_Supplier; GetValue ($suppliers ; $i )] End Loop  
      When I run it, however, it creates zero New Records within my "Month" database.
      I'm not sure what the issue is. My only hunch is that the List function isn't properly creating a List of the "Suppliers::UUID" field, and is causing the ValueCount taken of it to return 0.
      Please let me know of any additional information I can provide, and thank you in advance for any assistance you can offer!
    • By scchang
      FM 16.
      I would like to have new field entries automatically be added to the value list for the same field. I tried searching but wasn't able to find any references to this. Apologies if it has been previously addressed and appreciate any advice. Thanks.
    • By NewBoard
      I have stumbled upon a strange bug in one of my Databases concerning a value list.
      We have a table containing part numbers, which is related to a table containing "revisions" for the part numbers, which is related to a table of inspections for the "revisions".
      On a layout of the Inspections table, I have a value list. This value list selects the UUID of the revision, but displays the Part Number for the user.
      This has worked perfectly for several months. However, this morning one of my users discovered that he was unable to located "0000194" from this list. At first I thought maybe the UUID's somewhere along the line had become disconnected from their respective foreign keys on related tables. This was not the case. I then tried deleting and remaking the value list. This did not solve my problem. Then I figured if I deleted and rebuilt everything related to 0000194 that would fix it. This was not the case either. As a last ditch effort I restarted our FileMaker server, to no such luck.
      So then I began playing around with various aspects of 0000194. First I changed the company name, but it still didn't show up on the value list. Then I changed the part number to "TESTPART", and it showed up perfectly. So I tried "0194" and that showed up. Same for "00194" and "000194", but as soon as I add a fourth zero making it 0000194, it disappears from my value list. I'm 95% sure that this part number used to show up on the value list just fine, as we've already done inspections using this part number. So I'm thinking this bug has something to do with the leading zeros. I'm unsure of how to debug this or if there's any workarounds for this issue.
      Let me know if I need to supply any more information.
      Thank you in advance for any help you all can offer!

      [SOLUTION] At some point one of our scripts was run by a user without full permissions. The script added part number "0000194" to another customer, which I never noticed. Since Value Lists are indexed, it was not showing "0000194" because it was not a unique value. Deleting the second occurrence of "0000194" cleared my problem up.
    • 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.
  • Create New...

Important Information

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