AnsweredAssumed Answered

Need to update a field from another table using a calculation

Question asked by mark.ogier on Aug 14, 2017
Latest reply on Aug 15, 2017 by mark.ogier

Hi

I am an experienced .NET & SQL programmer but a total newbie when it comes to FM. So please excuse me if the answer to this is obvious.

 

I have tried for ages, googled for hours and read the manual (honest ) and am really stuck.

 

Here is what I want to do.

 

I have a main table of Items which I are being sold on-line. There is separate table, just a reference table, no relationships to the main Item table, which has a collection of records with information about packaging mailers.

 

In the main Item table there is a field called 'Width'.

 

In the reference table (Packaging) there are (currently) only 4 records. Each record has the following fields

Mailer_Type (Text)

Weight (Number)

Width (Number)

Cost (Number)

 

I want to find the weight of the Mailer which will be the one to use based on the Width of the Item entered on the screen in the Width field of the Item table being able to fit into the Width of the Mailer. (and eventually use the cost to calculate postage etc)

 

So I have a field in the Item table called 'Package_Weight'. I have set the options for this as Auto-Enter, Calculated and am using an ExecuteSQL to try and fill the value. The ExecuteSQL stamens is as here

 

ExecuteSQL ("SELECT Min(P.Weight) FROM Packaging P WHERE P.Width > 150"  ; ""; "")

 

As you can see, I am currently just using a hard coded value of 150, not the actual value entered, that will be the next question!

 

The reason for the MIN in the select is that there will be a number of packages that have a width greater than 150, I want the smallest one that is still greater than 150. Hope that makes sense. (eventually of course I want to use the ACTUAL width entered not a fixed 150 value...)

 

Bottom line is, my Item.Package_Weight field is not being populated......

 

I am sure I must be doing something stupid, please can someone help me?

 

Thanks

Mark

Outcomes