Fun problem - data organization
Dear Forum members, have I got a challange! We are trying to see if Filemaker can be a fit for an inhouse pricing system, we are a shipping company.
In principle, we price our shipping by zone (A, B, C) and weight (1lbs, 2lbs, 3lbs). In reality there are also aspects of different pricing for type of packages (envelopes vs package) and different surcharges as well as one international zone/pricing and one domestic zone/pricing - but that is sort of next step after sorting out the basics. We also have different price lists that we assign to different clients.
I am trying to figure out how to organize the pricing data to be able to look up a price when I enter a shipment. At the time of shipment, I will know where it is going and who is the client. I need to call a zonelist to get the right zone (based on country (international) or zip (domestic), then call a pricing table with the weight to get the right price.
I was thinking I would create different pricing tables with rows being weight and columns being zones. Then I could create a table with relationsships between clients and the right pricing table and use a variable in the script to call the right tablename. But it seems complicated to maintain all the relationships between the shipment table (weight) and the N number of pricing tables and I think I need to have the relationships in place to be able to call the pricing data.
Perhaps it makes more sense to dump all pricing in one big table and add a column called pricelistname and reference both weight and pricelistname when looking up a price for a certain wieght and zone? Zone will be in the column that I request, I will use a variable in the script for that I think.
Any comments or ideas?!