Jump to content

Count list of values from Summary list field


Recommended Posts

Hi guys!

I hope someone can help me with this.

I have a SUMMARY LIST field which contains types of items eg. (shoes, shoes,  sneakers, boxers, jumpers, boxers, jackets, shoes, sneakers...). These items are also listed in a value list (boxers, jumpers, shoes, jackets, sneakers)

Is there a way how I can generate a count list of items by and in order of the value list? Taken the example above,  this count list would look like this (2, 1, 3, 1, 2). The commas here should actually be the carriage return.

Tnx a lot! 

Link to post
Share on other sites

Please edit your profile to reflect your version and OS, so that we know what you can use.

The task, as presented, requires looping over the value list and counting the corresponding values in the summary field. For this, you need either a custom function (requires the advanced version), or the While function (requires version 18 or higher), or a looping script.

However, it needs to be said that there is something wrong with this request. Filemaker provides built-in methods for summarizing data - provided that the data is structured properly in records and fields. In your example, if you have a record for each item, you could simply sort the records by item type and use a summary field to count the the number of records in each group. 

 

Link to post
Share on other sites

Hi!

Thank you for replying so fast! I updated my profile, sorry about that.

Yes, you're completely right. The thing is, I was wondering is there another way how to do this by calculation and avoiding the usual procedure. Besides, I would like to have, in the end, the list of sums of each item type so I can use it for charting.

 

 

Link to post
Share on other sites

Sure. But I would like to design the charts according to delimited data. So far, the summary list fields and ValueCount ( FilterValues ( MyList ; MyItem ) worked really good. However, now I stumbled on a situation where MyItem is not anymore an item from fixed list of values but an item from the list of values that can be expanded with new values.

You probably have a better suggestion. My idea was to look for a way how I can automatically generate a list of sums according to the current state of the value list.

Link to post
Share on other sites

Sure. But I would like to design the charts according to delimited data. So far, the summary list fields and ValueCount ( FilterValues ( MyList ; MyItem ) worked really good. However, now I stumbled on a situation where MyItem is not anymore an item from fixed list of values but an item from the list of values that can be expanded with new values.

You probably have a better suggestion. My idea was to look for a way how I can automatically generate a list of sums according to the current state of the value list.

Do you think that something like Fast Summaries would be of any use for this?

Link to post
Share on other sites
18 minutes ago, Sinky said:

But I would like to design the charts according to delimited data.

No, you wouldn't. You will see why in a moment.

In order to produce a return-delimited list of individual count of each value, you could do a calculation like this:

While ( [ 
listOfTypes = YourSummaryField ;
uniqueTypes = ValueListItems ( "" ; "YourValueListName" ) ;
n = ValueCount ( uniqueTypes ) ;
i = 1 ;
result = ""
] ; 
i ≤ n ; 
[ 
result = List ( result ; ValueCount ( FilterValues ( listOfTypes ; GetValue ( uniqueTypes ; i ) ) ) ) ;
i = i + 1
] ; 
result
)

This will return a list of counts of each value, in the order of the value list. But for a chart - esp. if it is a pie chart - you want to list the most frequent values first. This is very easy to do if you let the chart tool work as intended (i.e. chart the found set, with a data point for each group) and sort the records by type, descending  with reorder based on a summary field that counts the records. Replicating this functionality in a calculation would be very difficult.

 

  • Like 1
  • Thanks 1
Link to post
Share on other sites

Charting is one of many aspects of FM with which I don't have much experience. What I'm about to say therefore might not sound reasonable. One reason why I would like to avoid charting by the found set is because I'm trying to build a dashboard with several charts and I would like that these charts are independent from the sort or find changes in a table view. Therefore I opted for delimited data.

That being said, your calculation works like magic and provides exactly what I had hoped for. Thank you a million times! 🥳🥳🥳

Link to post
Share on other sites
4 hours ago, Sinky said:

I'm trying to build a dashboard with several charts and I would like that these charts are independent from the sort or find changes in a table view.

Ok, that is reasonable. But in such case you want your delimited lists to be produced by a script and written into global fields or variables. (Note the plural form: you need one list for the X axis and another for the Y axis - and you need to produce them in corresponding orders.) Which means the chart must be produced/updated on demand.

IMHO, the Fast Summaries method would be indeed the best way to produce such lists. Alternatively, you could also use ExecuteSQL().

 

Edited by comment
  • Like 1
Link to post
Share on other sites

Great! Thank you for taking the time to help me and provide some insight. I will look into your instructions and figure the rest out. Respect!

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
  • Similar Content

    • By AndreasvanHaren
      How can I count the amount of lines in a text field? And is there a way to get a live update of the line count while typing in the field without it slowing down or interrupting the typing? 
    • By Peterteneldas
      Hello, everyone, here’s my latest question. I hope this is the proper area to post it.
      I’m creating a flat-file movie database to keep track of where my movies are in my home. I added a serial number field, which gives me a running total of how many movies are in my collection.
      But I think I need something different, and am requesting help for creating it. I have several duplicate movies in my collection, and I don’t want the duplicates included in any total count of my movies. At least, not in my forms or reports. I don’t care if duplicates get serial numbers, and I don’t care if the count in the toolbar (i.e. 516 unsorted) counts them all. I understand that the serial numbers and toolbar count are for “records.” But I want a total count of my movies, excluding duplicates.
      What I’ve done is create a checkbox for duplicates. If the box is checked, then of course, the movie is a duplicate. Is it possible to create a field that has a running count of the movies in my collection, EXCLUDING the duplicates? If it is possible, what is the procedure from omitting them from any such subtotal, summary, or running total?
      Thank you! PT
    • By -dp-
      I have a layout that includes two sub-summaries. The topmost includes a summary field called studentGradeCount, which counts the number of students in a given grade.
      The subordinate sub-summary contains applicationStatusCount, which is a summary field providing a count of applicationStatus.
      Adjacent to the status count, I would like to provide a percentage, indicating what percent of the students have applications at each status. Logically, it would be
      applicationStatusCount / studentGradeCount
      But FileMaker doesn't return anything when I attempt this. Any help would be appreciated.
      FMPA 16.0.3
      macOS 10.13.3
       

    • 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 bcooney
      I'm running into an issue hiding a text object in a sub summary part when a summary field in the same part is empty or zero.
      I created a little demo file to show my issue. I resorted to using Get Summary, but that still doesn't work in my solution (but it's much more complicated report there).
      I'd appreciate feedback as to why I needed Get Summary.
      tia,
      Barbara
      summaryhide.fmp12
  • Who Viewed the Topic

    1 member has viewed this topic:
    Wickerman 
×
×
  • Create New...

Important Information

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