Total a field then add to temp table

[ckai]

This is an interesting one for me as I can do everything else to this problem but the most important.

What I'm doing is adding the required records from one table to a temporary table so I can export in the right xml format (for invoicing). I would use the source table for all the information apart from this one item - travel.

Everything else gets itemised apart from the travel in which I just need a total of all the trips.

The easy way out would be to itemise it as well but this would double the entries for the invoice and make some clients invoices several pages long.

So the question is, how would I go about totalling the Travel field for each client then transferring the data to the temp table? I can do the transfer part (using variables and set field) but I can't work out how to do the summary field.

I quickly worked out that creating a layout with a summary part doesn't help as you can't set that field in the layout as a variable. So I'm thinking it's a script and field defining solution.

Any ideas?

[ckai]

mmm 30+ views and no ideas. Bugger. Are others looking for answers as well or did I confuse everyone?

[Raybaudi]

I say mine... I didn't understand the itemising concept.

[comment]

If (!) I understand this correctly, there are two ways to do this:

1. Have your XSLT stylesheet summarize the travel category. This is preferable, because it doesn't litter your solution with something that's required only for the export;

2. Define a calculation field like =

Case ( Category = "Travel" ; Category ; ItemID )

Sort and group by this field.

I don't see why you need a temp table here.

[ckai]

haha I thought it was the case of not explaining myself.

The reason for my temp table is because I'm not really that clued up enough on XML and the calculations for it. And also because certain entries need different information so I was thinking along the lines of pulling all the information I need, putting it in the required fields in the temp table, then it would be a simple export to xml.

The format of where I'm getting the info from is:

shiftID

staffID

ClientID

Date

Start time

Hours

Travel - $ amount

Staff designation - basically there pay rate

Rate

Total cost

So each of these shifts are pretty much shown as is in the invoice so all the details are sent to the temp table. Although the format for the xml is different so the fields change in the temp table. Eg:

Hours = quantity

StaffName = notes

Staff Designation = category

I would like the travel to just be one entry for the total amount for each client (if there is any). For example the temp table entry would be something like this:

Client: Best client in the World

Quantity: 5 (total amount/client travel rate)

Notes: 5 return trips

Amount: $250

Rate: $50 (client travel rate)

Category: Travel

Hopefully that's cleared a few things

I might try and look into totalling using XSLT and like you said comment I may be able to avoid using the temp table.

I'll also look into the sorting and grouping because that's given me another idea that might sort it.