4 Replies Latest reply on Dec 30, 2016 9:46 AM by philmodjunk

    Simple Calculation Troubles


      I am having some simple (appears) calculations problems. I am building a inventory system for (mainly) ordering products. We have 2 sellers we go through, and situations change which one we need to buy through- so I have 2 fields for price on every products.


      My relationships run as follows



      Invoice::ID = InvoicePart:InvoicePartID [Allowed Creations]

      InvoicePart::ProductID = Products:: Products ID [Used for LookUp]


      In InvoicePart(which I use for storing the product ID and invoice ID with amount needed) I have a base total cost(InvoicePrice::Distributor1Cost) for both distributors calculated from (exactly) [ Products::Distributor1Price * amount needed] - which gives a blank slot. Then I have [ Sum(Invoice::TotalDistributor1Price)]. The sum is obviously not going to work, as InvoicePart::Distributor1Cost comes out as blanks.






      Not sure what I did, but it shows up now as just "$1.00" for all of them despite updating "InvoicePart::AmountNeeded"

        • 1. Re: Simple Calculation Troubles

          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.

          • 2. Re: Simple Calculation Troubles

            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.






            • 3. Re: Simple Calculation Troubles

              tleitzke wrote:

              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.

              • 4. Re: Simple Calculation Troubles

                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.