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 w...
Forum Discussion
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.
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.