Extracting/capturing Subsummary values

[mickeyfinn]

I have a job ticket database with 3 main tables, Ticket, Bill and Customer. The structure of the database currently is one where each record in the Ticket table contains quite a large number of tally fields, i.e. one quantity (number) field per service offered. The reason it is organized this way is that the customer has a billing preference that we cannot alter.

We have to end up with a report breakdown of each ticket in a table with the services provided to the customer in columns across the top. I would obviously like to see the database converted to a more powerful line item system (adding a Line Item table) so that new services can be added easily (amongst other reasons), but I still need to generate the breakdown in the format the customer demands.

To my mind, this could be done if I could make calculation decisions based on the subsummary totals of each ticket per ticket (subtotals) and per job number (which would be my grand totals). I would also have to paste/show each ticket item description across the top, for each service used in that bill. This would be a script which analyzes each line item, and where each related subsummary total per job number shows a value greater than 0, a column with that service is created.

Previously, I have made the billing system a separate table (the Bill table) with subtotal fields looking up related values from the ticket table with relevant summary and calculation fields for totals and subsummary grouping for the grand total, total per story (many tickets make one story) and options to group per page number (most but not all pages are more than one ticket). To bill a job, you run a script which creates these related records and then displays the report. I realize that I may be better off doing this from the main table and am open to that if it helps solve the problem.

I have tried to grab these totals, but I realize that from the perspective of another table, these values don't really exist (since they depend on the sort order of a foreign table). Still, the benefits to us for converting to a line item system are considerable, so I was hoping there was a way to "cheat" this into working.

I hope I've given enough information, this post has gone through a number of revisions for clarity. To sum up the problem: how can I extract/capture subsummary values to import into another table, and make script/calculation decisions based on these values so I can restructure the report from a standard line items system to a spreadsheet-like overview where each service is now a dedicated column instead of possible values in a single service description column.

Thanks in advance to all who reply.

[Søren Dyhr]

each record in the Ticket table contains quite a large number of tally fields

Could you provide us with a screenshot of this table (preferably the entire relational structure), because it sounds as if these need to broken out in yet another relational structure ...in order to make anything summarize ... in general should the report be made from where the data exists most granular!

But your description doesn't bring me closer to the bigger picture, but if we take this statement:

how can I extract/capture subsummary values to import into another table, and make script/calculation decisions based on these values so I can restructure the report from a standard line items system to a spreadsheet-like overview where each service is now a dedicated column instead of possible values in a single service description column.

Does the entire idea with import sound as something not fully embraced by this community - which are known to leave the data where it's stored in the first place. But what the result should be is pretty clear a x-tab report, which isn't something filemaker does natively, some trickery is required:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

--sd

[mickeyfinn]

That link you provided is great. The cross-tab report is exactly what I'm trying to do. I just didn't know what it was called before. I've not really used repeating fields all that much. I'm starting to see how powerful they can be. I have a further question:

(Quote is from provided link)

With certain additional effort you can make dynamic reports, i.e. don't start with January all the time, but display whatever months you have in your found set.

This function is referenced but not explained. I believe I can assign a number to each service easily enough. How does the report omit columns that are not included in the found set?

By the way, I will post some screen shots of the database I'm working on soon. It's become a bit messy of late because I'm trying to redesign it so I'll tidy it up a bit first! :)

[Søren Dyhr]

How does the report omit columns that are not included in the found set?

Excellent challenge, I came to think of these:

http://www.briandunning.com/cf/62

...and the CF used in this:

http://www.filemakerpros.com/GetNthRecordTAIL.zip

I would then make two calc's:

c_theMonth = Right(0 & Month ( theDate );2) ....result in text!

...and

c_Usher = Case ( Get ( CalculationRepetitionNumber )=PositionValue ( FilterValues ( "01¶02¶03¶04¶05¶06¶07¶08¶09¶10¶11¶12";SerialNumbersTail ( Extend ( c_theMonth ) ; Get ( FoundCount ) ; "" ) ); Extend ( c_theMonth ) ; 1 ; 1 );Extend ( Amount ) )

This at least does it when you make a found set - only the required columns will show up sorted from the left towards right. But my guess is there might be place for some refinement??

--sd

[mickeyfinn]

Thanks so much Soren for the fast and concise response!

OK, I think I understand most of thos. The first calc assigns a text format, two-character number (month number) so it can appear in a text value list. The second calc uses the custom function PositionValue to assign a number to a filtered list of values from 01 to 12 (only including those that appear in the found count in the correct order). Thus, if found records only contained January and March, 01 would get a value of 1 and 03 a value of 2. This then assigns January records to repetition 1 and March records to repetition 2. What I don't understand yet is what the custom function SerialNumbersTail does. I looked at the cf in the file in the second link and it seems to involve itself in a case calculation. Can you tell me what this does? Sorry, I am a novice!

So, from what I can tell, this technique has a limit of 12 cross tab columns (or whatever values you enter into your FilterValues function). For my application it would be nice to have an infinite number of possible occurances (since the list will be filtered to records only having a grand total of > 0). I could still make this work by making the number of values in the FilterValues function long enough that the limit is difficult to reach, and has an error capture if the maximum is exceeded. I can even reduce this by having possible services filtered to only those related to each customer. Still, it would be nice not to have a limit at all...

[Søren Dyhr]

Can you tell me what this does?

Since we're talking summaries, is the found set essential, this means we need to grab only those values of c_theMonth that occures in the set, otherwise would the entire calc' be worthless, and you could easily live with the one Edoshin provided you with.

For my application it would be nice to have an infinite number of possible occurances

This means that repeating fields isn't the correct way to solve it ... due to thier obvious upper limits. However could my hardcoded string in the first part (leftmost) in FilterValues( be replaced by a ValuelistItems( over an additional cartesian relation, in order to ignore the found set and to make each value only occure once, this would make things behave more openended.

--sd

[mickeyfinn]

I might just be getting better at this. I was just thinking about using ValueListItems to open up the possible maximum number of items within the found set. The actual limit on the number of repeats is not a problem since I will not want too "wide" a report. The data I'm collecting will not create too many columns anyway as there are a lot of services that are "either/or" options with each customer.

Just so I can understand the calc fully, I see that SerialNumbersTail involves giving serial numbers to items in a found set somehow, but only to unique values (I assume). I see that the field you are checking for unique values is entered first (field), followed by the found count (found), I just don't know what the result parameter does, and why it is "" in this calc.

[Søren Dyhr]

but only to unique values (I assume)

Take a look, or experiment a little to see how:

http://www.filemaker.com/help/FunctionsRef-346.html

...works. What you will learn is that the values ushered thru is the ones from the left parameter, so the sort order they represent gives the way the data is aligned. This mean if you have:

"01¶01¶01¶02.... in the right paramter, will only the single occurrence from the left paramter get thru to the result. Hence the use of ValueListItems( utterly trimmed appearance.

--sd

[mickeyfinn]

Yes, I understand what FilterValues does now, also what the neat function PositionValue does (I know so many uses for this already). It's still the SerialNumbersTail I am not sure of. I've been playing with it and it seems to always return an Enter-delimited list of all values of the specified field including repeated values. The Found parameter affects the number of results returned. However, the Result parameter seems only to return whatever you place here as the first item in the list. What is the Result parameter used to do?

Thanks for being so patient explaining all this. I have begun to implement this in a test database I built to experiment with this report. It's already working well.

[mickeyfinn]

So, I'm almost done with this, but one last detail is eluding me. I need to have corresponding labels to put at the top of each column in the xtab report. I can do it with a script loop but surely there's a calc that will do this?

I can get each label to show individually when the related record is selected, but I either need to summarize these fields (not sure how to summarize text like this) or have the labels as a global field and populate from a value list. I'd like to do the latter (since it seems more efficient) but I only know how to set one repeating field at a time. Surely there's a way?

[Søren Dyhr]

Take a closer look at the calc' it's already in there, the same principle stands for splitting or ushering the values to repeaters that Edoshins site shows.

--sd

[mickeyfinn]

It looks like I'm missing something (probably very basic) here. As I understand it, each record with its month value is taken by the calc and its value is put in the correct repetition. So only one column for each record in the body part will show a value. Using subsummary by another field will allow us to see values across all months in every row (in the crosstab example you posted, the subsummary is by the "category" field). In this way, just by changing this:

c_Usher = Case ( Get ( CalculationRepetitionNumber )=PositionValue ( FilterValues ( "01¶02¶03¶04¶05¶06¶07¶08¶09¶10¶11¶12";SerialNumbersTail ( Extend ( c_theMonth ) ; Get ( FoundCount ) ; "" ) ); Extend ( c_theMonth ) ; 1 ; 1 );Extend ( Amount ) )

To this:

c_Usher = Case ( Get ( CalculationRepetitionNumber )=PositionValue ( FilterValues ( "01¶02¶03¶04¶05¶06¶07¶08¶09¶10¶11¶12";SerialNumbersTail ( Extend ( c_theMonth ) ; Get ( FoundCount ) ; "" ) ); Extend ( c_theMonth ) ; 1 ; 1 );Extend ( [color:red]MonthName ) )

...should return a label in each record in the correct repetition, but all the rest of the repetitions in that record will be blank (since each record only contains one month value).

I tried various subsummary options to see if I could get all the month values to show in each repetition at once in a subsummary part, but I can't find any way to summarize text like this.

I admit that there may be some alternative way of using the SerialNumbersTail cf that I'm missing. I still only understand what it does, rather than how it does what it does. However, it seems to me that the above calc cannot push more than one value per record to its appropriate repetition. Again, there may be something very obvious I'm missing.

[Søren Dyhr]

Yesterday I lacked internet connection, so I couldn't get to the CF's. This meant that I needed to make some new ones, but take a look at the attached template.

Please note the CF I in the template calls "Gather" isn't tail recursive so it would easier be "overwhelmed" by the measure of data!

But basically does it the same thing... I didn't include ValueListItems( over a cartesian relation, hence the FastSort( CF.

--sd

ColumnZap.zip

[mickeyfinn]

Thanks Soren, I've been working with the calc you sent in your example file. It took a little bit of time to figure out how to adapt it to a value list but I think I get it now. I'm not quite understanding what you mean by the gather cf being "overwhelmed". Does this mean that I have a limit on the amount of records it will return in a list?

I'm so grateful for all your help in this. You have helped give me the one answer I need to start to redesign my solution so it is more efficient and expandable. Cheers!

[Søren Dyhr]

Does this mean that I have a limit on the amount of records it will return in a list?

Yes indeed it is, read the instructions in the JMO template saying:

Shaun Flisakowski, the calculation engine guru on the development team at FileMaker, Inc., apparently reads my articles in FileMaker Advisor. Hats off to him for not locking himself in a closet and not paying attention to how developers are using the product. Anyhow, he read my article about GetNthRecord recursion which is covered in greater detail in the file you are using right now. In this file and the article, I mention that recursion is limited to 10,000 recursions. If you run the GetNth Append or GetNth Prepend scripts, you will not be able to gather more than 10,000 serial numbers. If tail-recursion is used, the full 50,000 limit can be utilized. Basically, tail-recursion allows the result of the recursion to be taken out of the stack, thus avoiding the stack limit of 10,000 iterations. There is still a 50,000 iteration limit that is imposed to make sure the calculation doesn’t get stuck in an infinite loop. Anyhow, that’s as best I can describe it since I am not a C++ programmer.

The rest of these notes are the same as the first file I posted on GetNthRecord. Well, I have changed a few things below but not much...

There has been a technique floating around the internet for years (thanks to Darren Terry) which demonstrates how to store and restore found sets. The technique basically collects serial numbers from all the records in the found set into a return-separated list. When a user wants to restore a found set, all they have to do use a button with a Go to Related Record attached.

Well, there is a faster way to collect a multi-key with FileMaker Pro 8 using the GetNthRecord function. All you need is a recursive custom function and a script with a single Set Field. Custom functions require FileMaker Pro 8 Advanced to be inspected but they will still run under FileMaker Pro 8. This solution will also measure the elapsed time so you can compare the different techniques. However, you will find the following when testing:

1. GetNthRecord collects serial numbers in a fraction of the time as a looping script.

2. Recursive functions can only recurse 10,000 times (unless you use tail-recursion which can be much slower).

3. The GetNthRecord technique doesn’t require the script to leave the current record, preserving it for a better user experience.

4. TheGetNthRecord technique can be used in a calculation field with no need for a script.

When I first started performing these tests, I noticed the speeds varied a lot. I was finally able to ascertain that looping values into a script variable was far faster than looping them into a global. But, I was still getting variances each time I tried testing from a new file. I finally noticed that appending versus prepending the serial number to a global or script variable actually made a difference. I found the following with 5,016 records:

- Global Loop Append = 48 seconds

- Global Loop Prepend = 31 seconds

- Variable Loop Append = 13 seconds

- Variable Loop Prepend = 5 seconds

- GetNthRecord Recursion Append = 10 seconds

- GetNthRecord Recursion Prepend = 3 seconds

- GetNthRecord Recursion Prepend TAIL = 3 seconds

- Copy All Records = less than 1 second

When conducting these tests I tried to make them as fair as possible by looping on a blank layout, freezing the window and opening a new window to preserve the current record. If you are using FileMaker 8.5, prepending versus appending makes no difference.

Speed is not the only determining factor when choosing a method to employ. For example, the Copy All Records technique is the fastest but it marries the script to two layouts and destroys the contents of the clipboard. On the other hand, the Variable loop requires no special layouts but does require FileMaker Pro 8. Make sure you consider the advantages and disadvantages when choosing your solution.

Although the focus of this tip is to demonstrate the difference in speed between the different methods for collecting serial numbers, the scripts are complete in that they will store the serial numbers and restore the found set. In other words, this is a complete solution for storing and retrieving found sets.

I also recommend viewing the GetNthRecord example in my 2005 Developer Conference presentation which is also available for download from the Resources area of databasepros.com. It show how you can use the GetNthRecord approach to grab all the related values without a value list and the ValueListItems function.

Copied, from this templates global field: http://www.filemakerpros.com/GetNthRecordTAIL.zip

However does other approaches exist:

http://www.clevelandconsulting.com/support/viewtopic.php?t=1264&highlight=overcame

So this is pretty expandable, keeping the fact from perhaps the certification test in mind here that there is a 2 GB upper limit to what fields can contain of data, remembering that each ¶ pilcrow counts too.

--sd

[mickeyfinn]

Thanks Soren for all your help in this. I think I've got something together that will work, which I could never have done without your help. Sorry it's been a while posting, I've been traveling.