7 Replies Latest reply on Sep 16, 2010 10:38 AM by philmodjunk

    Vendor Solution Needed

    Bricktop

      Title

      Vendor Solution Needed

      Post

      Hi All,

      I'm making and invoice solution with me being the middle man. I need a field to show the price based on vendor choice and Item. All the items are the same, but the price varies per vendor daily, weekly, monthly. My first soultion I made each item id unique with different daily,weekly,monthly, prices. It didnt get good feedback because it made them items list way to big. 10 items * 5 vendors = 50 things to sort through.

      I need to able to pick an item (not unique), pick vendor (from a list), and the the  field will calculate the price based on start date and end date. I hoping to do this using a line table. Im hoping somone can help.

      Thanks

      Steve

        • 1. Re: Vendor Solution Needed
          philmodjunk

          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

          • 2. Re: Vendor Solution Needed
            Bricktop

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

            • 3. Re: Vendor Solution Needed
              philmodjunk

              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.

              • 4. Re: Vendor Solution Needed
                Bricktop

                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.

                • 5. Re: Vendor Solution Needed
                  philmodjunk

                  THis is the tables/relationships I was picturing:

                  Invoices---<LineItems>---Rates

                  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.

                  • 6. Re: Vendor Solution Needed
                    Bricktop

                    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?

                    • 7. Re: Vendor Solution Needed
                      philmodjunk

                      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.