Copying Data from One Portal to Another

[maluktuk]

We're developing a new quoting system for our company. We have items from suppliers that we will use repeatedly in different quotes. Each item is different and their suppliers may offer one price regardless of quantity or one or more price breaks depending on quantity. Because of this, we have an "Item Price Breaks" table that allows us to add one or more price breaks for each item we sell that can be unique to each item. The main pieces of data captured in those price break records is quantity (e.g., 1-100, 101-200, etc), per (e.g., each, case, etc), and then the price.

 

We then have a separate quotes table in which we can add additional information based on the type of job. We first select a vendor and then an item from a value list of only those items supplied by that vendor. We'd like to pull the related price breaks into the quote and add some additional fields (e.g., decoration cost, total product cost, as well as a gross profit calculation) to the price break data. For example, if we had an item "mug" and there were three price breaks (1-50, 51-100, and 101+) we'd like for that information to populate lines in a portal (3 to match this item) in the quotes table but have additional fields available for data entry.

 

Is this type of thing possible? How could we make something like that work?

[IdealData]

All of this is quite possible, and relatively easy once you understand a few more things about relationships.

 

Related tables are not restricted to single 'match' criteria, but can have multiple criteria. Not only this, but you can use different operators between the fields in the criteria. These operators are

 

=

< (less than)

<= (less than or equal to)

> (greater than)

>= (greater than or equal to)

<=> (not equal)

'x' (all, or any)

 

Using the '<=' and '>=' operators will help you define a range. As you have already defined an 'Item Price Breaks' table then you would be using the low and high values in each break as the 'range' for your relationship

 

In your particular situation you will need to extend your tables to include QuoteLines - that is a table to hold the multiple lines you will want to create on a single quote. Do not make the mistake of using repeating fields because you might think they're easy - they will hurt you later in your development!

 

Presuming that what you want to do is enter a quantity in the QuoteLine, then the associated cost/price values are derived from a record in the 'Item Price Breaks' table that the entered quantity lies between. viz.,

 

ItemPriceBreaks::lowqtybreak <= QuoteLines::quantity >= ItemPriceBreaks::highqtybreak

(Do not take my table::fieldnames as specific as they are only intended to help you visualise)

 

You will need the following relationship to capture the cost/prices from the ItemPriceBreaks table:

 

QuoteLines::SupplierID = ItemPriceBreaks::SupplierID

QuoteLines::ItemID = ItemPriceBreaks::ItemID

QuoteLines::Qty >= ItemPriceBreaks::LowBreakQty

QuoteLines::Qty <= ItemPriceBreaks::HighBreakQty

 

I hope the above makes sense.

 

If not, I have a commercially available solution which does all the above and much more!