You want a Summary field type most likely instead of using Sum() as a calculation.
A summary field type will summarize data from the current found set in the current table.
Sum() will summarize RELATED data from another related table.
Covered in the documentation: FileMaker Pro 15 Help
Excel is a flat, non-relational format, so all calcs are designed to work in the current sheet. Thus a lot of the functions, although named the same, perform much differently.
Much depends on how you implemented this:
I have a database where users can go in and choose to buy devices.
How your system is set up to build a list of devices selected for purchase will then determine how such a total might be computed. A summary field or a calculation field with the sum function are both options,but both have to be implemented correctly.
To advise you on that requires that we know how you have set up your system for selecting devices for purchase.
A typical method uses these tables and relationships:
Where a portal to LineITems is placed on the Invoices layout in order to list the items selected for purchase in a given sales transaction. Note that you may have this setup but with different table occurrence names.
But what have you set up?
It is currently set up where there are four different devices available for purchase and each on has a costperdevice field. The user has the options to buy all, buy individual machines, return some, and return all.
Thank you, but that does not answer my questions
Sorry, I think I am in a better spot but now I have this type of question
I am doing a calculation of buycountertotal * costperdevice, this works great if the costperdevice is one value. The problem is there are multiple devices in the same record causing different prices per device. Is there a way to make it work to calculate the value of each device based on the price rather than one value?
You need to store a record for each device purchased, that way you can have an independent price on each line.
For example, You are trying to create an invoice with a number of different prices on it, but you need ANOTHER table of InvoiceLines that will store prices for each item.
In InvoiceLines, you have qty * Price to calculate the cost of each unique device.
Then in Invoices, you have Sum (InvoiceLines::subtotal), which will give you the total for invoice.
It sounds like you are missing that “lines” table in your database.
See reply #2