Weird problem

[Robin Smith]

First, I don't really know how to describe this without going into great detail so forgive me if I give too much info, but here goes:

I have a table that I want to be able look up info in. It is a log scale table and I need the user to enter the width and length in fields in an embedded browse on a form. For example, for the Scribner scale, I would like to use the width (diameter) is what would be in excel terms the row and the length is the column. But since I can't use just numbers in the column(field)name, I started it with an "S". For example, field S1 is one foot in length, fieldname S20 is twenty feet in length.

I can easily use the width to lookup the appropriate row but need to somehow use the length number to determine which fieldname or column to select.

In excel, I would use HLookup or Vlookup to find this intersection of the correct column and row, but I don't know how to convert a number to a fieldname to find the column.

Thanks in advance,

Robin

[PhilD]

RE: Weird problem

Robin,

I won't pretend to know anything about scaling logs, but would it not be easier to simply calculate the result?

Scribner:
V = (0.0494*Dia*Dia*Length)-(0.124*Dia*Length)-(0.269* Length)

Where 10 is Dia and 12 feet is length:

V =(0.0494*10*10*12)-(0.124*10*12)-(0.269*12)
?v
= 41.172

0r:

V =(0.79*Dia^2-2*Dia-4)*Dia/16

V =(0.79*10^2-2*10-4)*12/16

?v
= 41.25

The formulas give sligtly different results and I just quick pulled them off the web as examples. I would find out what your using and use that formula.

Anyway,
you could have two variables Dia and length. Enter values in these variables, run a script to plug the values in the formula and calculate the result. You could also have list boxes for the values that would populate the correct variables upon selction of list values.


Phil

[Tom Cone Jr]

RE: Weird problem

Robin,

If the field names follow a consistent pattern, such as
S10
S11
S12
S13

and if the numeric portion of the field name corresponds to a particular length, you could get the field value using the Eval() function.

Here's what I mean.

In Alpha Five once the record pointer is moved to the right row, the value stored in any field is available to you using just the field name. You don't have to do a lookup or use a function like the ones you describe for Excel. The entire record is in a buffer, and its elements are accessible using the field names.

So, if your script knew the desired field name it could retrieve the field value like this:

tbl = table.current()
vCurrValue = tbl.S10

In this example the variable vCurrValue would receive the value in the S10 field from whatever row is currently selected in the table referenced by the poiner, tbl.

In your case, the script knows that each field name begins with an "S" and your user is supplying an "length" value that will vary. How to retrieve the field value from the current row of the table?

Well, the trick would be to build a string representing the field name, and let the Eval() function process it for you.

Here's an example from the Interactive Window, using the attached table. If you save the attachment to disk do so using an empty folder.

tbl = table.open("Scribner")
tbl.index_primary_put("ByDiam") 'set index needed by fetch_find
tbl.fetch_find(12) 'fetch the desired row

?tbl.S12   'this line just shows you the field value
= 144.000000

?eval("tbl.S" + "12")  'this line show how to use eval()
= 144.000000

tbl.close()

-- tom

[whanigsberg]

RE: Weird problem

Robin,

I have a question about your approach which has spreadsheet overtones (ominious music here).

If your length values are fixed and small, that is, values from 1-20 with new values unlikely you can work as you have it set up. However, if you ever have to enter a new length you will need to edit the table definition.

On the other hand, if you had a table with a record for each Width-Length combination adding new values would only involve adding records. The lookup would still be simple and (if properly indexed) fast.

Bill

[Robin Smith]

RE: Weird problem

Phil,

The only way to get the correct standardized info is from a table. I wish there was a consistent formula that could be worked out for this. :)

[Robin Smith]

RE: Weird problem

Hi Tom,

You have come to my rescue again...

So would I use a variable tied to the onchange event for the "length" field on the form to populate the numeric part of the eval()? Would there be a better way to do this?

The table shows 40 possible lengths and 17 possible widths, which (although I hadn't even thought of this before) if I put this into separate records would mean more than 600 records.

Just to give some addl info, this is something that is going to need to be called up often and the options of lengths and widths shouldn't need to change.

I appreciate all of your help,

Robin

[Tom Cone Jr]

RE: Weird problem

Robin,

You should experiment with different ideas for the interface the user will see. It certainly is possible to create a variable in the properties of your form, and then drop that variable on the form. You could then either use an event like OnDepart or OnChange I suppose , or maybe a separate button altogther, as the hook upon which to run the script and calculate the field value.

-- tom

[Peter.Greulich]

RE: Weird problem

If you don't want to use a formula, you could create a table with 18+ fields. Then enter 20 records which gives you your 20X17 spreadsheet like matrix. The first field would the length; the other 17 would be the width/value: Field names might be W1, W2, W3...with the value you want to lookup. etc. etc.