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.
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.
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.