Simple lookup script for returning a price on a table
Hi,l am very new to scripting and this program. l have a table with prices and measures for blinds, a simple x y chart with width of blind running across the top and drop running down the side, the table is then filled with prices that fall between each size. I am looking for a script that will return the price if l set the width and drop on a form. Example. @ 2450mm x 1645mm blind would cost ..where ever it landed in the table, taking into consideration prices and sizes are incremental. Sounds confusing but l would love your help. I will have a form field width, drop and dollar amount and would like to return the $ amount after manually filling sizes.
6 replies
-
How do you view comments.
-
Let“s suppose that the table containing the prices is called ”Measures“. The script below is intended to be the ”On click“ item of a button located in another table, where ”Width“ and ”Drop“ are the dimensions of the window, and ”Price“ is to receive the result of the lookup.
let zMyWidth := Width;let zMyDrop := Drop;let zFirstWidth := first(((select Measures)[Width > zMyWidth] order by Width).Width);Price := first(((select Measures)[Width = zFirstWidth and Drop > zMyDrop] order by Drop).Price) -
It would be better to replace the two occurences of “>” by “>=”...
-
Thank you for your help. This is my first time setting up a data base and it has been a looooong journey, this is the last peice to my puzzle. I will give this a try today. Really appreciate the help.
-
Hi Alan , I have entered your formuka and it is returning a value but not the right one. Here is the Formula I have used ...
let zMyWidth := Width;
let zMyDrop := Drop;
let zFirstWidth := first(((select “Gill Roman”)[“W Width” > (= zMyWidth)] order by >= “W Width”).“W Width”);
Price := first(((select “Gill Roman”)[“W Width” = zFirstWidth and “W Drop” >= zMyDrop] order by “W Drop”).“W Price”)the Width and Drop will fall within a range of the table pricing, so not sure if a greater than or less than equation is needed.
The Reference table is setup as W Width W Drop WPrice. and pricing is incremented as is the Sizes.
Hope this helps and appreciate your advise.
kind Regards
michael
-
Mmmmm, the third line is not exactly what I sent, so I am not suprised that it does not work correctly. Anyway, that third line should be replaced by the simpler version below, not forgetting to replace the double quotes by simple quotes to undo the vandalism of the forum shitware.
The greater or equal comparisons are indeed needed. The logic is to select, for each dimension in turn, the lines where the tested dimension is greater or equal than the desired blind, and then to keep the line with the smallest value.
let zFirstWidth := min((select “Gill Roman”)[“W Width” >= zMyWidth].“W Width”);
Content aside
- 3 yrs agoLast active
- 6Replies
- 801Views