Forum Discussion

MarkCorcoran's avatar
MarkCorcoran
Qrew Cadet
8 years ago

Relationship Help (VLookup Senerio)

Need some help figuring this out... I tried and tried, but just can't get the result I need.

I have two tables (Calculator and Pricing).  I would enter information into the calculator form and it would pull pricing from the pricing table, very much like vlookup does in excel.  There are a couple of fields on the calculator form that determines what price needs to be looked up.
The first is the "colors" field (one for each print location)  this number is an exact numerical match to the record ID# of the pricing table.
The second is the quantity, this number would need to be used to determine which field ID# the price needs to be pulled from (works as price breaks by volume).
This the look of the calculator form view.

Below is the look of the pricing table.


Any idea of how I can make the form pull in the correct pricing based on quantity of garments and number of colors (for each location separately)?  I've had no luck at all.

Thanks
Printmark

2 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    This should be relatively easy.

    Set up a relationship where 1 Pricing has many Order Lines.  You will in fact get this working for the first line and then have to duplicate the relationship 6 more times.  But let's get the first one working.

    Let the system create a field on the right side of the relationship and called it [# Colors for Line 1].

    Edit that field to make it a formula field  and make the formula just equal to the field value that you now type the # colors into.

    The relationship could be built directly based on the data entry field so you may want to try it both ways to see which data entry experience is better.

    Then lookup all the fields on that record for the various price points.

    Then make a formula to calculate the price.

    IF(
    [Qty Line 1] < 24, [Price Break Line 1 under 24],
    [Qty Line 1] < 48, [Price Break Line 1 under 48],
    etc
    [Qty Line 1] >= 471, [Price Break Line 1 over 471])

    get that far and see how it goes for the data entry.

    If you build the relationship based on the actual data field for the colors, then it will become a multiple choice drop down field which you may or may not like as much.  I'm not 100% sure if the form will calculate live though during data entry if you make the relationship based on the formula field. 
  • MarkCorcoran's avatar
    MarkCorcoran
    Qrew Cadet
    8 years ago
    It worked like a charm! 

    Where I was losing it was trying to make everything work off of a single relationship.  It never occurred to me to make a separate relationship for each location.

    Thank you, I'm sure glad you guys are around to help, 

    Printmark