1 of 1 people found this helpful
I'm lost as to what you're trying to accomplish with that calculation, but I think I know what you need...
You do not need any summary fields.
Add a calculation field to the line items table. Field Name: "Line Price"
If ( Custom Price = "", Regular Price ; Custom Price )
Then, in your parent table, the "Total Discount" is Sum ( Lines::Discount ), and the "SubTotal" field is Sum ( Lines::Line Price )
Edit: for clarification
Do you meant I need to create an extra field call 'Line Price'?
Yes - in the line items table.
what is the idea behind adding then subtracting the same field in a formula ?
Here's my current calculation:
Case(S_Custom Price = ""; S_Regular Price - S_Discount; Custom Price ≠ ""; Regular Price - Regular Price + S_Custom Price - S_Discount + S_Regular Price)
If the cashier enter the custom price, then the regular price for that service will not be used, but use custom price instead.
But my calculation statements didn't add the other line services that don't have custom price.
Case is nice when it makes sense, in your case I see a better alternative in
FinalPrice = If ( IsEmpty(S_Custom Price); S_Regular Price; S_Custom Price) - S_Discount
I'll try you guys solutions later, and post my feedback.
Thank you all.
siplus, the fields starting with "S_" are summary fields, which I don't think you intended to use.
If you switch to the regular fields and put this calc in the line items table, then the difference compared to my solution is that you are deducting the discount at the line level, so that it will be excluded from SubTotal. OP should bear this in mind and be clear about whether he wants SubTotal to be net of discounts, but I think you're probably right about this.
Your solution is working for me, you're good.
Somehow the Sum ( Lines::Line Price ) didn't working for me.
I made a slight modification to the Subtotal price is that I have to use the Summary result of Line Price.
SubTotal = S_Line Price - S_Discount
Another question: Is there a way I can count the unique number of the Technicians on the invoice?
The summary fields are unnecessary. You could eliminate 2 fields by changing your SubTotal Calc to:
SubTotal = Sum ( Table::Line Price ) - Sum ( Table::Discount )
(Replace "Table" with the name of the child line items table occurrence)
To define a field that shows the count of the unique technicians on the invoice, try this...
ExecuteSQL ( "SELECT COUNT ( UNIQUE technician ) FROM LineItems WHERE foreignKeyInvoiceNumber = ?" ; "" ; "" ; InvoiceNumber )
Replace "technician" with the name of the field containing the technician name or number.
Replace "LineItems" with the name of a table occurrence of the line items table
Replace "foreignKeyInvoiceNumber" with the name of the field in the line items table that is the invoice number
Replace "InvoiceNumber" with the Invoice Number field from the Invoices table (which is where this calculation will be defined)
It's interesting to see your graph... am I understanding correctly that "Register" refers to what would commonly be called an "Invoice", and "Invoices" refers to the line items belonging to that invoice? But if that is the case then is not InvoiceIDFK the primary key?? and ID in "Invoices" is the foreign key???.... very confusing! Can you post your original layout but show it in layout mode?
For technician count, I wrote "UNIQUE" when I should have wrote "DISTINCT". knowing the field names and the above assumptions I believe it should look like this:
TechnicianCount should be defined in the Register (?!?) table and =
ExecuteSQL ( "SELECT COUNT ( DISTINCT Technician ) FROM Invoices WHERE ID = ?" ; "" ; "" ; InvoiceIDFK )
At the beginning, the Technician was in the Register DB, but if I do that, then every time I add a new technician with new services, it'll override the previous technicians.
Therefore, I store the tech's name in the Invoice db.
In this layout, everything is belong to Invoices db, except Technicians, and InvoiceNumber fields.
They belonging to Register db.
This is really bizarre - there are so many problems with this it's hard to know where to start.
Normally you would have a table called "Invoices" which would contain fields such as invoiceNumber, date, time, subtotal, total discounts, tips, grand total, tendered, change, due, payment type, customer information, etc.
And you would have a table called "Line Items" that would contain a foreign key (e.g.: invoiceNumber), description, price, discount, quantity, technician, etc.