We have to start with the basics just to be sure:
In what table is Total defined?
What kind of field is it? Is it a calculation field or a number field with an auto-enter calculation?
What is the calculation expression used to compute that total?
Total is defined in the Order Details table.
It is a number field with an auto-enter calculation.
The calculation is Price * Quantity. (Both fields in the same table).
I should add that this field worked fine for months and only recently behaved strangely. I'm not clear what I did to confuse it. First there was the missing data, and now what appears to be incorrect data.
And is the "do not replace existing value..." check box selected?
is Price * Quantity the exact expression you'd get if you pasted the expression from FileMaker to here?
Have you recently changed any part of this calculation?
Examine the layout while in layout mode. Make sure that Total has been selected from exactly the same table occurrence as that specified in Portal Setup|Show Related Records From. If it is not, change this so that it is from the same table occurrence.
As a fix, you might change the field to type calculation. This will keep the same calculation expression but force an update of the calculated value. You can then experiment with changing it back into an auto-entered calculation.
That only fixes the current values, it doesn't explain why it is wrong in the first place.
PhilNo - do not replace existing value is not checked.Yes, I copied and pasted the expression.I don't think I changed the calculation recently. When I look at an earlier saved version of the database it works fine, and I can't see any differences between them. Is it possible the database is corrupted?Layout mode - ok I examined it and actually the set up is more complicated than I initially explained:The main table being used is the Contacts table.
Within contacts table the totals fields are from different tables.The portal is for the Orders table. Some of the fields are from Order details (like the fields in the calculation) and some of them (like the date and paid status) are from Orders 2 (a second instance of Orders table).I tried changing this around to see if it improved things, but it didn't:If I change the portal to reference the Order Details table, it works just as well, but the problem with the Total field remains.If I change the portal to reference Orders 2 - it shows the same reference numbers in the first two fields of the portal for each item.If I change the fields to reference Orders instead of Orders 2, it seems to work just as well.This is probably TMI - but I'm attaching an image of the relationships in the database.Calculation - I changed the field to a calculation, and again the data showed up correctly in the table, but not in the portal. I changed it back to an auto-calculation, and the problem remained.Thanks in advance.Sam
Sorry, but there are quite a number of missing details in your last post. Are not total, price and quantity from Order details? But the portal is to Orders?
That won't work. What you should get in that case is values from the first related record in Order Details, which will not show a combined total based on all related records in Order Details.
If you want the total value of the entire set of related records in order details, you need to refer to either a summary field defined in Order Details or a calculation field in Orders that uses the sum function to calculate the same total from the set of Order Details records linked to each Order record shown in the portal.
But your original screen shot seems to list only a single product for each order. So I remain confused as to what you actually have set up here. Are you sure that this portal isn't a portal to Order Details?
Some of the fields are from Order details (like the fields in the calculation) and some of them (like the date and paid status) are from Orders 2 (a second instance of Orders table).
That definitely adds to the confusion here--especially since you do not identify which fields reference which table occurrence!
Sorry for not explaining things better.
All the fields in the portal (including quantity, price and total) are from Order Details, only date and paid status are from Orders 2.
Yes the portal currently is set to Orders. When I changed it to Order Details, I found it worked the same, but also the problems were the same.
I am not looking for combined totals, just the total of quantity x price in each Order Details record.
I don't think I can address the possible issues with your total field until your basic layout design makes sense to me.
What do you want one row of this portal to represent? What I see looks like you want to list all items from all orders for a given contact record. If each item purchased is a separate record in order details--the typical set up for an ordering or invoicing system, then it makes no sense to base the portal on Orders. Even basing it on Order Details is somewhat problematic as you appear to want to combine data about the order as a whole with specific data about each purchased item. Either you base the portal on Orders and only see data for the first Order Details record or you base the portal on Order Details, get each item purchased to list in your portal, but now the date and paid status fields repeat over and over on each row.
But I am making some assumptions here that may not be correct. Does each order list multiple items--each a record in order details? Or does each order consist of only one item type to each order?
Final note: If this is FileMaker 12 or newer and if the only row that shows data for Total is the first row in the portal and this is the case for any record you select in Contacts, enter layout mode, drag the total field out of the portal, release the mouse button and then drag it back into the portal row. Move the portal a pixel or two (ctrl-z or undo will pop it back into its original position) and make sure that all the fields in the portal row move when the portal is moved. This addresses why the data only appears in row 1 of the portal but not why it would compute the wrong value.
Thanks - once I pulled the field out of the portal and put it back in, all the data showed in each row of the portal, and the sums were correct as well.
You're right, this layout could give me some problems in the future. So far I am doing one item per order and this seems like a convenient fix for now, but I'll have to change this as the product range expands. Any suggestions, please let me know.
To make a suggestion would require that you decide how you want this portal to work for you. Currently, you seem to want it to do two different and slightly incompatible things. One option is to use two portals, one to orders and one to order details--such as a pair of "master-Detail" portals where you'd click to select an order in the first portal to perform a script that updates the second portal to list the order details for that one order. See this thread for details on how to set it up: Need layout solution for nested portals...
But that is just one of many options that you might explore.