"Combine/group" records for exporting

[ckai]

I thought of several ways I could do this but always come up with snags. I couldn't even think of an accurate title that explains exactly what I'm after (maybe why I haven't been able to find anything through searching). Hopefully those higher up on the food chain can give me some direction

In short, I'm looking to combine or group certain records that get exported to our invoicing software. We do this in the invoicing software manually but thought I'd attempt to cut out the middle man.

What happens for this invoice export is I loop through records, storing the relavant data in variables, then add these records to a temp table that is formatted in a better way for exporting. I could possible do it without the temp table but it was one of the first things I developed with the limited knowledge I had. And it works fine which is the main thing.

Anyway, certain client gets a more "summarized" invoice than the others. An example of the data

Client Staff Hours Total Date

Client 1 Jones 2 hours $20 1/7/12

Client 1 Jones 3 hours $30 2/7/12

Client 2 Frank 2 hours $20 1/7/12

Client 1 Fred 3 hours $20 3/7/12

Client 1 Jones 1 hours $10 7/7/12

What I'd like to get put into the temp table instead is something like this:

Client 1 Jones 6 hours $60 1/7/12

Client 2 Frank 2 hours $20 1/7/12

Client 1 Fred 3 hours $20 3/7/12

The dates for these clients are actually irrelevant as a date range is added to the invoice.

Of course the grouping doesn't need to happen when I add the record to the table. I could add all the records then perform another script to find the required clients, group them in some way, then delete the records that are then not needed. I just thought getting the records into the table the right way would be the best.

The export is xml and I thought of grouping the records using a stylesheet. The problem is that the grouping would occur across all the records for every client which I wouldn't want so it seems doing it with FM is the way to go.

Any ideas/directions would great. My problem is that I'm unsure how to combine all the required records together into the right variables so they can be added to the temp table.

Thanks

Edited August 8, 2012 by ckai

[comment]

When exporting, you will get the option to 'Group by [Field]' - provided records are sorted by that field first.

The export is xml and I thought of grouping the records using a stylesheet. The problem is that the grouping would occur across all the records for every client which I wouldn't want

Not sure what you mean by that.

[comment]

Upon reread, I have decided I don't understand your example. How come both Jones and Fred are Client 1?

[beverly]

you said "Anyway, certain client gets a more "summarized" invoice than the others." so that means YOU know which clients are "more summarized" and the database does not. Do you propose to have some sort of field set to FLAG these exceptions? if, so perhaps you can create a calculated summary for them. Alll others would not have these values.

Also please post which version of FMP you are using.

Beverly

[ckai]

When exporting, you will get the option to 'Group by [Field]' - provided records are sorted by that field first.

Not sure what you mean by that.

I thought of doing the grouping at export but I don't want every record grouped, only the clients that require them. This is the same reason for not grouping the filemaker created xml file using a XSLT, I only want selecte clients grouping.

Upon reread, I have decided I don't understand your example. How come both Jones and Fred are Client 1?

Sorry, when I was trying to remember everything I thought would be needed I forgot to put some headings. I've edited my original post to include them. Poor choice of names actually as I should have kept continuity and used staff 1 etc instead of names.

I also see it displays a bit jumbled up unfortunately. Hopefully it's still understandable.

you said "Anyway, certain client gets a more "summarized" invoice than the others." so that means YOU know which clients are "more summarized" and the database does not. Do you propose to have some sort of field set to FLAG these exceptions? if, so perhaps you can create a calculated summary for them. Alll others would not have these values.

Also please post which version of FMP you are using.

Beverly

No, the database knows which ones get summarized. They're known as Funder (there is a field in the clients table called client_type) and all their records to be invoiced are added to the temp table via a separate script (fun_invoice).

I could do one export for non-summarized invoices then another for the summarized ones but each time the xml file is created it will, of course, overwrite the previous one. Meaning we would have to export, complete those invoices, go back to FM, export again, and complete those invoices. A little counter-intuitive so I've gone against this idea.

I thought about a calculated summary. Couldn't quite get my head around how I could work it out though.

The way I see it is during the fun_invoice script I would "group" the records (as this script works with only Funder records), maybe storing this information in variables, then add them to the temp table. I'm thinking a loop script that checks if the previous record matches (based on criteria). If so, adds it to the variable, if not, the variable gets added to the temp table.

As for the version of FM, does it not show on the left side? I'll have a look at that. It's 11 anyway.

[comment]

I am afraid I still don't understand your example. Perhaps you should add field headings and the contents of the ClientType field. Or attach a file that shows a report summarized the way you want it (if that's possible).

In general, it's easier to let Filemaker group the records upon export than having a stylesheet deal with it; However, if the required data is there then there is no reason why a stylesheet couldn't do it just as well.

[ckai]

I've been trying an idea for the past 2 days hence my lack of response.

For some reason I can't edit the original post, and unfortunately I can't really make the example any easier to understand (tried to - I think I'm stuck in my ideas thought ).

But hopefully to make things easier I thought I'd really simplify things:

So this is the data that needs to be worked with:

Client Staff Hours Total Date

Client 1 Jones 2 hours $20 1/7/12

Client 1 Jones 3 hours $30 2/7/12

Client 1 Jones 1 hours $10 7/7/12

I want is to look like this when it gets added to the temp table (for exporting):

Client Staff Hours Total Date

Client 1 Jones 6 hours $60 1/7/12

So since the 3 original records have the same client and staff, they get grouped into one entry with the hours and total added together. The date is irrelevant so it just uses the first.

What I've thought of doing is within my loop, if the client and staff matches the previous record, add the hours and total to a variable then move on to the next record. If they don't match, the variables get added to the temp table. I've got it to partly work but, using the simplified example, it's grouping everything for client 1 even if the staff member is changing.

In my real data, I actually have a number of criteria that needs to match not just the 2 for the example but I thought it would just create a lot more confusion. It would more than likely be easier to post an example but I'm unable to do so.

I know my loop structure isn't up to scratch, so I'm just chipping away at it to sort it out.

I've certainly come to the conclusion this can only be done in filemaker.

[comment]

I am afraid we're not making real progress here. Your last example is trivial: you'd simply sort the records by client and by staff, and export them grouped by staff. This is assuming that records for Client1 by Smith deserve another line.

[ckai]

Your last example is trivial: you'd simply sort the records by client and by staff, and export them grouped by staff.

Exactly, this was one of the reasons why I was trying to think of another example. Basically, this is only one part of all the records to be exported and are the only ones that need grouping. The other records can be exported as they are without grouping.

Funder clients get grouped

Facility clients don't

Hence my logic behind a temp table so the records, grouped and not, get exported in one hit, in the same single file. This single export file contains both Funder and Facility clients. My examples only based on Funder clients since that's where I'm having the issue.

Never mind. I'll eventually crack the sucker!

Appreciate your time. :hairy:

[comment]

Funder clients get grouped

Facility clients don't

Well, if I take this as a simplification of the problem, then perhaps you should look at the attached.

ConditionalAggregate.fp7.zip

[ckai]

Well, if I take this as a simplification of the problem, then perhaps you should look at the attached.

Wow, that's KISS at it's best! It actually took me a bit to get my head around it since clearly I was over thinking things. To be honest, I feel like a bit of a muppet after seeing this now. I use a similar technique to create our staff wage export file but didn't think of using a calculation to do the sorting (dunno why not).

I've worked out an interesting calculation that takes all my criteria into consideration and with a little bit more tinkering will be perfect.

How many times do you shack your head when people over complicate things? haha

Thanks again.