4 Replies Latest reply on Oct 18, 2015 5:25 PM by fmckinnon

    Need Input/Guidance on Layout

    fmckinnon

      Hey Gang,

      I've been using FileMaker for years but haven't had to build a layout in forever.  I'm on FM v11.

       

      Our team uses FileMaker for the data entry of orders in our e-commerce business.  We typically capture the order date, order #, the marketplace where item sold, the vendor we buy from, the gross sale amount, and the vendor COGS (costs of good sold), so that we can get a net sale amount.  Below is a screenshot of what we've been using for YEARS.

       

      Screen Shot 2015-10-18 at 5.43.02 PM.png

       

      Lately, we have two big issues with this layout and I could really use your advice on how to take it to the next level:

       

      #1:  we have GROWN tremendously and instead of manually entering a lot of these orders, we are not bulk uploading the from CSV/TXT files that we get from marketplaces or vendors.    This is good, however, it's introduced a problem because of #2:

       

      #2:  in the past, the majority of our orders have products purchased from only ONE vendor, so when we manually enter an order or when we upload the vendor's billing statement csv/txt file, the amount we upload into "Vendor COGS" was accurate.  However, now we have dozens of vendors, and one single Order # (PO field above) may have costs associated with multiple vendors.  So, when we upload the invoices from Vendor A, it may overwrite the costs already entered or uploaded from Vendor B, etc.

       

      SIDE NOTE:  The Net Profit/Loss amount is a calculated field that's more complex than just Gross Rec - Vendor COGS, etc -- it also includes merchant fee % reductions, etc., based on what Marketplace is chosen.

       

      Hope that makes sense so far.

       

      Moving forward:

      To get accurate data, I planned on creating a separate field for EACH possible vendor, so that moving forward, our staff will manually type in the invoice amount for each vendor into it's exclusive field.  (likewise, if we are uploading 100 invoices from Supplier A, we can upload them into Supplier A's field, etc).   We'll change the current Vendor COGS field to be a calculated field that sums up all possible Vendor COGS fields.

       

      That seems simple, but the layout is where I get stumped.  Most orders have 1, or possibly 2 vendors associated with the purchase.  Rarely, an order may have 3-4 --- but there are probably 30+ possible vendor options.  (You notice above we typically choose what vendor in the "Vendor" field, and even on orders where we have 2 vendors, we've just picked one -- so the data is already not good).

       

      UPLOADING invoices and Vendor COGS data is easy enough, and the layout isn't critical - but we still manually enter hundreds of orders per day, and I want to create a layout that is efficient for our staff to quickly navigate.  I thought about a tabbed section where each vendor had a tab, but that could get crowded quickly as well.

       

      How would you suggest it?    What's the most efficient way to design this order entry layout for our staff to quickly and easily choose a vendor, and enter that vendor's invoice amount and move on, assuming there are multiple vendors.

      For example, is there a way if I choose "Vendor A" from the Vendor popup menu, that a Vendor COGS field exlusive for Vendor A shows up for data entry (if I choose D&H as my vendor, the DHVendorCOGS field appears) and then if there is another vendor on the same order, I can click a + sign or some element to bring up another "Vendor" popup box to choose from, etc.

       

      I know I can just build a huge box with two columns:  Vendor and Vendor-Specific COGS and list all 30+ possible options, but that seems inefficient.

       

      HELP!   Much appreciated.

      Fred

      https://www.pinterest.com/pin/create/extension/

        • 1. Re: Need Input/Guidance on Layout
          erolst

          fmckinnon wrote:

          To get accurate data, I planned on creating a separate field for EACH possible vendor, so that moving forward, our staff will manually type in the invoice amount for each vendor into it's exclusive field.  (likewise, if we are uploading 100 invoices from Supplier A, we can upload them into Supplier A's field, etc).

           

          Without really looking further into your solution, you can be sure that the (almost always) better way to implement multiple vendors per invoice is to add a new table, like VendorInInvoice.

           

          Your '+' button will then create a new related record / portal row, ready to receive a new vendor selection.

          • 2. Re: Need Input/Guidance on Layout
            fmckinnon

            erolst thanks for the quick response.  It's been a really long time, so help me think this through.  I actually have one additional table already associated with the layout, called "Marketplaces" where each Marketplace record has a % transaction fee and a $ transaction fee that is added.

             

            So, if I create a new table, it would have, what .. fields:
            Vendor Name and Vendor-Specific COGS?

             

            How do I actually add and implement this "+" sign concept ... on the layout - is that in a portal? 

            • 3. Re: Need Input/Guidance on Layout
              erolst

              fmckinnon wrote:

              So, if I create a new table, it would have, what .. fields:

              Vendor Name and Vendor-Specific COGS?

               

              I think so (I'd have to be more familiar with your solution).

               

              Just imagine: if you'd create dozens of fields for every possible vendor(ID), you'd also need the same number of fields to fill in the (current) COGS, and the same number of relationships to auto-fill that COGS value, and huge & unwieldy calculations to add it all up.

               

              That's why invoices have related line items record, rather than countless fields for fields like item1, price1, item2, price2 …

               

              fmckinnon wrote:

              How do I actually add and implement this "+" sign concept ... on the layout - is that in a portal?

               

              Yes; in the simplest incarnation, just have a script add a new portal row (related VendorInInvoice record) and scroll to that row so the user can select a vendorID.

               

              Even better would be a Cartesian relationship to the Vendor table so you could show a selection portal of all vendors (and do some error trapping, like disallowing duplicate vendors per sale); but since 11 doesn't have popovers, you'd have to place that portal onto the layout, and that may too much space.

               

              Or maybe open open a new window with a list view of the Vendor table …

              • 4. Re: Need Input/Guidance on Layout
                fmckinnon

                yeah - you just went waaaaay over my head, lol ... the smart thing for me to do is hire somebody to build this out for me.  There used to be a place in the forums where you could do that ... (I had to create a new account, couldn't seem to recover my old credentials)