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.