Convert data in List to different format using calculation engine

[ehwalker]

I'm trying to convert data contained within a list to a format that can be used for a google chart. If at all possible, I would like for the conversion to take place within a single calculation field and I think there should be a way to handle it using the While function, but I'm struggling to get the configuration right and was hoping someone here more capable could provide some direction.

Input List

2 October 23,Employee 1,10336
2 October 23,Employee 4,1500
3 July 24,Employee 3,2500
4 July 24,Employee 2,5228
4 July 24, Employee 3,1038
4 July 24, Employee 3,3700
4 July 24, Employee 4,3700
4 June 24, Employee 1,1276
4 June 24, Employee 1,206

Output List

Date, Employee 1, Employee 2, Employee 3, Employee 4
2 October 23, 10336,0,0,1500
3 July 24, 0,0,2500,0
4 July 24, 1482, 5228, 4738, 3700
 

The function would need to iterate through the inpu list and sum the values if the date and employee numbers match and return a value for every employee (even if 0) for every date. Also, I would need the calculation to be able to handle lists of varying length with varying numbers of Dates and Employees. I've attached a small file with the list I need to convert and the desired output. Perhaps there's a better way of achieving this that I'm ignorant of, but I would prefer it all be handled within a single calculation field. Really appreciate any help! 

ListConvert.fmp12

[comment]

Such manipulation is certainly possible, using either the While() function or a recursive custom function or even a looping script.

However, it is far from being a convenient way to process data. Filemaker is designed to store data in a structure of records and fields - and it has plenty of built-in tools to assist in processing such data. In the given example, sorting the data by date and by employee, and summarizing the amounts (?) using a summary field would allow you to produce the expected result much more easily.

If your data is actually structured properly as a table of Employees and a child table for the amounts (where each amount is a separate record with fields for Date, EmployeeID and Amount) then I would suggest you start there instead of producing the "Input List" first then try to wrestle with it.

 

[LaRetta]

And if you receive the data exactly as you've indicated, I would first convert it into tables and records as Comment suggests.  Converting the import can be handled in a single looping script.  Once normalized, you can generate reports, place portals on an Employee's layout, and anything else you can imagine. 😀

[ehwalker]

Thanks for the feedback comment. I have a 'master dashboard' (unsure of the actual jargon) that displays these data in various ways, summarizing "amount" by employee ID, job ID, etc. and that displays some of these using google charts via a webviewer.

36 minutes ago, comment said:

If your data is actually structured properly as a table of Employees and a child table for the amounts (where each amount is a separate record with fields for Date, EmployeeID and Amount) then I would suggest you start there instead of producing the "Input List" first then try to wrestle with it.

The data are structured as you mention, but I'm unsure how to produce the output I listed above using built in features?

LaRetta, the 'input list' above is the result of a calculation field in my 'master dashboard' table that returns unique values from a summary field in the child (amounts) table. 

[comment]

1 minute ago, ehwalker said:

The data are structured as you mention, but I'm unsure how to produce the output I listed above using built in features?

I would use a script for this. I believe you need a script to get the data to Google Charts anyway, so that could be just a part of that.

The tricky part here is that you need a "cell" in each line for every employee, even if they have no amount on that date. So I would do 3 preliminary steps: first, get a list of unique dates (sorted in chronological order, unlike your "Input List"). Then get a list of unique employees. Then use the "Fast Summaries" method to get the actual amounts to be charted. I would store these in a JSON object so they can be easily retrieved in the final step.

The final step would use two nested loops: first, create a line for each unique date. In each line, create a cell for each employee and retrieve the corresponding amount from the JSON (which of course will return nothing for employees with no amount on that date).

---
BTW, just for my own amusement I ran a script to summarize the "Input List" provided in your question. I had to correct it first because some lines have a space before the Employee N value. But the values I am getting are quite different from the ones shown in your "Output List".  For example, you show the amount of 1482 for Employee 1 on 4 July 24 - but Employee 1 has no entry at all on that date!

 

 

[ehwalker]

Thanks for the tips! I figured maybe I would need to go to a script for this. I'll lookup the "Fast Summaries" method you mention and read up a bit on JSON as I've not used it much, and give it a try.

-----

Sorry for the errors in the lists I provided in my original post, I made some manual modifications to them for this post and I guess I should have been a bit more careful to keep everything consistent and accurate! 

[Søren Dyhr]

I would leave the looping for the google sheets side of the matter, and only do this:

Substitute( List( "theDate"; "Employee"; "Amount" ); ¶; Char( 9 ) ) &
¶ & Substitute( ListConvert::inputList; ","; Char( 9 ) )

...on the filemaker side of it. The data in the OutputList, could then be copied, and then cmd-shift-V pasted into a waiting google sheet. When the formatted data has arrived into the sheet, the place the cursor somewhere in the middle  of the pasted, and issue a pivot table command....Well I've made a film showing how to:

 

Skærmoptagelse 2024-07-28 kl. 18.47.02.mov.zip

 

--sd

Edited July 28 by Søren Dyhr