This isn't the best approach. Should you ever add a third distributor, you will need to make extensive design changes to your solution. There are two approaches that avoid that issue. One is to use different product IDs for each product for each supplier. Another option is to add a table for supplier pricing that uses both a product and a supplier ID in order to supply the needed price.
That said, what you describe should work so some detail isn't quite right. I suggest uploading screenshots of your relationships and copy/pasting the exact calculation that is producing an empty result.
We will only ever have these 2 suppliers that we order from. So I do not want an extra table creating redundant extras.
Looking around, I may have come to the conclusion that the auto-calculation is just not updating as it should- which could make sense why nothing is changing. But I cannot find anything online for how to make the auto-calculate calculate again without retyping some command in every input box and every touchable surface.
Looking around, I may have come to the conclusion that the auto-calculation is just not updating as it should- which could make sense why nothing is changing.
The reason for this is that your calculation is syntactically correct (you can close the Edit Calculation dialog box without FM complaining), but semantically - well, not ... ie it's not what you meant.
You don't have to (indeed, must not) refer to the field itself in the auto-enter calculation in this way; if the field name is TotalGenscoCost, then this
TotalGensCoCost = InvoicePart::AmountNeeded * Products::GenscoPrice
is a Boolean expression, and thus will not evaluate to an extended price, but just to True or False, ie 1 or 0, depending on whether you initially have an amount or not (probably not, so empty field reference = 0 * price, which yields Boolean True = 1 = 1$ ... )
What you want is simply this:
InvoicePart::AmountNeeded * Products::GenscoPrice
but I'd advise you to do it this way: in the InvoicePart table, have three fields:
price (auto-enter calc: Products::GenscoPrice)
amountNeeded (manual entry)
extendedPrice (amountNeeded * price, calculation)
This will preserve the price as of the time of the invoice without you having to re-calculate it - and an invoice typically states the individual product price, so you need that indicidual bit of data anyway.
PS: That all pertains to the correct method of copying over the price from a field in a related Product record.
How you will accommodate different prices for the same product from different suppliers is another matter; but having a separate table for SupplierProduct with foreign keys for product and supplier, plus a calculated supplierID in InvoiceParts, a relationship
InvoiceParts::id_product = SupplierProduct_forInvoiceDetails::id_product
InvoiceParts::cID_supplier = SupplierProduct_forInvoiceDetails::id_supplier
and using an auto-enter calc of
— which is basically what PMJ suggested — would be a) not really complicated, b) straightforward and b) infinitely scaleable.
We will only ever have these 2 suppliers that we order from.
Not in this universe.
Companies go out of business. Companies discontinue product lines forcing you to find a new supplier for that product. New companies open and offer better prices...
All of which can leave you with more than two suppliers.