Use look ups with a relationship that matches several fields instead of just the Item ID.
A relationship such as:
LineItem::ItemID = PriceList::ItemID AND
LineItem::VendorID = PriceList::VendorID
can be used to lookup a price based on vendor and ItemID
Great Thanks Ill try it, I also have a price calculation field based on the start date and end date with this calculation:
Div (rental length ; 7 ) *equipment::price weekly + Mod (rental length ; 7 ) * equipment::price daily
so Ill it a try. Im pretty new to FMP so it takes me a bit Laratta another user helped me with the line above
Thanks For all the help!!
On further thought, it'd make sense to have one product record per vendor with three rate fields for the weekly, monthly and daily rates for that product.
Your lookup can then copy all three rates into a given lineItem record so that calculations such as the above can use them to compute the full cost. (You would look up (copy) these values so that you can institute rate changes without changing the rental amounts of previously created invoices.
I'm sorry you lost me, I'm pretty new to this, at first I made one product record per equipment+vendor with rates.
After reading your post before last, It was looking like I should be making a separate table from my equipment table and make a price list table that's what I was starting to do (wasn't looking forward to that).Your thinking I had it right the first time? I'm confused!!
What i was trying to get was two drop downs (vendor + equpiment) rental period(end date - start date) times the rates ( daily weekly monthly) and like you said on the your old post i referenced, if i than drop down and change vendor or equipment it changes price
I already have a lines table were the price on my invoice is going to pull from, so I'm not going to have to worry about the rate change on my equipment table.
THis is the tables/relationships I was picturing:
One record in rates can be for one item as supplied by one vendor.
You've mentioned 3 rates, daily, weekly and Monthly.
Define three such rate fields in LineItems and use looked up value settings to copy the current rates from the same record for all three rates--just have three rate fields in each record of the Rates table. That approach should make it a bit easier to maintain and update your rates.
Great, thats what I had at first I liked it but some others didnt because it created a big record list to search threw. I didn't mind it, others did!! I was trying to make it bit more user friendly not so much designer friendly, by doing it the other way but, it might be a bit out of my league? I was thinking for reports it might be more accurate with separate drop downs and calculation for price. I just wasnt looking forward to a vendors price list table this is what i was thinking
Invoices-----<line items>--------<equipment>-----Price List
does this make sence?
wouldn't that be Equipment-----<Price List?
where you have multiple prices, one for each vendor of that equipment item, for one piece of equipment?
If so, you'll need to link prices directly to line items by vendor and item ID while equipment would be linked to line items just by item ID.
Note that you can use conditional value lists to make your item lists shorter. You can also set up search fields with scripting that lets you enter part of an item's description field and get a clickable list popped up of matching items where you can then click one item to add it to your Invoice's List of Line Item records.