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! 

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. 

 

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.

 

 

But you don't need it for charting: the chart tool gives you the option to create a data point fo each group in a sorted found set.

 

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.

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?

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.

 

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

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 March 26, 20214 yr by comment

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!

I am having difficulty understanding how this translates into a calculation.  

Quote

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
)

Would you be kind enough to help me out either with a screenshot or a little more explanation.  I get that listOfTypes, uniqueTypes and n are field definitions but then I am lost.  Thanks in advance.

Which specific part are you having the difficulty with? Do you understand the basic principle of the While() function?

Please note that listOfTypes, uniqueTypes and n are NOT field definitions; they are variables - more specifically, initial variables of the function. 

Perhaps it would be more useful if you asked about your specific problem instead - see: https://xyproblem.info/

 

Edited August 1, 20232 yr by comment