Hi all,

This one has me really confused and I'm going round and round in circles trying to figure it out. It's been a while since I posted so hope that I manage to explain myself properly...

 

FM13 on a Mac with the data on FM Server.

Clients::__kpClientID = Customers::_kfClientID

Customers::ID = TickSheet::ID

 

I have a Sub-Summary report layout based and sorted on Clients::ClientName

Most of the fields on this report layout are from the Customers table but there are a couple from an events table called TickSheet.

 

So far, I can group the report by Client which lists all the events per Customer.

 

I have two Summary fields in the TickSheet table.

     1) _sCountWithBh (this counts the number of events plus a multiplier for double rate on certain days) Result = 32

     2) _sCountWithoutBH (this counts the actual number of events) Result = 31

 

I want to calculate the difference between 1) and 2) above so that it can be used in a description field. The result I'm looking for here is 1 so for example: "Billable quantity is 32 as 1 event was at double rate"

 

I need to show this as we may visit once per day, every day of the month which equals 31 visits. However, if one of those days was at double rate then I want to charge for 32 visits but also explain why in the line item on the invoice.

 

Does that make sense? Hope so, many thanks...

Brian.

3 hours ago, Brian said:

Customers::ID = TickSheet::ID

What does that mean? One Customer has many TickSheets? Why Isn't it Customers::CustomerID = TickSheet::CustomerID then?

 

3 hours ago, Brian said:

I have a Sub-Summary report layout based and sorted on Clients::ClientName

Is your report produced from a layout of the Clients table? That doesn't seem reasonable.

 

Note that the GettSummary() function will not work with a related breakfield. If your report is produced from the TickSheet table (as it seems it should be), then you will need to add a calculation field = Clients::ClientName to this table and use it as the breakfield (in both the sub-summary part setup and in the sort order - as well as in the GetSummary() calculation).

 

Edited January 20, 20169 yr by comment

3 hours ago, comment said:

What does that mean? One Customer has many TickSheets? Why Isn't it Customers::CustomerID = TickSheet::CustomerID then?

 

Is your report produced from a layout of the Clients table? That doesn't seem reasonable.

 

Note that the GettSummary() function will not work with a related breakfield. If your report is produced from the TickSheet table (as it seems it should be), then you will need to add a calculation field = Clients::ClientName to this table and use it as the breakfield (in both the sub-summary part setup and in the sort order - as well as in the GetSummary() calculation).

 

Thanks for replying and apologies, I obviously wasn't clear enough. Please see the attached screenshot.
One Customer has many events in the TickSheet table

The layout is based on the TickSheet table and has a Sub-summary part sorted by Clients::ClientName but I didn't know that I had to add a Calc field to the TickSheet table.

I've done that now and it works perfectly

Many thanks
Brian.