2 Replies Latest reply on Jan 20, 2015 5:33 AM by GeorgeTai

    Evaluating FM - Invoice Line Item



      Evaluating FM - Invoice Line Item


      I am evaluating FM and wanted to know if FM is able to solve my problem as below:

      My invoice line items consist of multiple unit of measurement with various pricing.  I need to make sure that once the user selects the Item, the descriptions will be shown in the second column, and the next column will display a dropdown of UOM (unit of measurement in piece, unit, box etc..) per the itemcode that is selected and pre-configured in the database.  Each unit of measurement will have a different pricing.  Once the user selects the particular UOM, then the pricing of the item by that particular UOM will be populated to the respective UnitPrice column.

      In summary:

      1.) User selects ItemCode from Dropdown

      2.) Descriptions of the Item is shown in the 2nd Column

      3.) A dropdown of UOM by the respective ItemCode is shown in the 3rd column

      4.) Upon selecting the UOM, the UnitPrice of the paritcular UOM is displayed in the 4th column.


      Your response to my question will allow me to decide whether to GO or NOT with File Maker!!




        • 1. Re: Evaluating FM - Invoice Line Item

          This can all be done with FileMaker or any relational database system.

          1) this would (beginner level) use a simple use values from field value list listing Item numbers and product descriptions. The user can use the description to be sure that they are selecting the correct Item, but the value list will enter the item number.

          2) The typical invoicing data model looks like this:

          Customer----<Invoices----<LineItems>-----Products (and/orServices)

          Customer::__pkCustomerID = Invoices::_fkCustomerID
          Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
          Products::__pkProductID = LineItems::_fkProductID

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          Descriptions and pricing would be looked up from Products. (a separate table is sometimes used to manage pricing) The description can be shown dynamically (updating a description in Products updates the description shown automatically) or it can be copied (auto-entered) from Products. In any case, your pricing data would be auto-entered from either Products or a related pricing table.

          3) a simple value listof UOM's can be used or a conditional value list can list just the UOM's relevant to the selected item.

          4) There are two different approaches that can produce identical results. The simplest is to have a set of pricing fields in products, one for each UOM. Corresponding fields in LineItems then auto-enter all of the pricing for all of the different UOM's. A calculation field can then use the UOM field in LineItems insdie a Case function to apply the correct unit price for the UOM selected.

          A different option is to set up a related pricing table with three fields: ProductID, Price and UOM. A relationship from LineItems matching by both ProductID and UOM could then auto-enter a single unit price--the one for the UOM selected in step 3.

          FileMaker comes with an Invoices starter solution. This starter solution will have all of the features I have described here except for the UOM controlled pricing feature.

          • 2. Re: Evaluating FM - Invoice Line Item

            Dear Phil,

            Yes, I have been developing similar system since the VB6 time, and now trying to build a complete web based solution with PHP.  I thought it would be good to have an OFFLINE ordering system develop using FM and have the data sync back to the backend database once it is completed.

            Your answer has been very helpful to me and I think I will go for FM once I have a few more issues ironed out.


            Thanks Bro,