A calculated fill-in from a lookup table?

[Marilyn Wallace]

I have one table named P_Employ and another named C_Basic. I would like to be able to fill in the "Employer" field in P_Employ with the name of the person's employer, and employer's city and state from C_Basic.

I have no problem creating the equation [trim(name)+", "+trim(city)+", "+state]. The problem is that using lookups in the field rules only seems to allow filling in one field directly from one field from the lookup, not using a calculation.

Is there a relatively easy way to accomplish this?

Thanks in advance for your suggestions.

Marilyn Wallace

[Al Buchholz]

Yes, fill in the individual fields on the target table and then combine them when you need to with a calculated or rtf field.

Keep data in it's elementary form. It's easier to combine than the split....

[Marilyn Wallace]

I already have the data stored once

Al,
I already have the data stored once in the C_Basic table. I planned to store it in a single field (rather than multiple fields to be combined later) for the following reasons:

- The data is already in the C_Basic table. I am putting it here as a convenience, rather than creating a set and linking to C_Basic to see the information.

- Since I have already devoted storage space in C_Basic with fields large enough to accommodate the largest entries, I would prefer to compress the information to a more modest size by trimming out the blanks and only storing up to 50 characters of the combination of name+city+state (which means in rare cases the state and part of the city will be eliminated).

Marilyn Wallace

[DaveM]

ok try this

If you really must combime all in a calculated field,

you can do a lookup on the name in an expression and add it to another lookup and add it to the third lookup. All in one calculated field.
Example:

trim(lookup(name,,,))+", "+trim(lookup(city,,,))+", "+Lookup(state,,,)

does this make sense?

Dave

[DaveM]

calc field

this is an actual that I use as an alternative:

compadd = lookupc("F","1","address","comp","group")
compcistzip = lookupc("F","1","cstzip","comp","group")
compname = lookupc("F","1","name","comp","group")
comp = trim(calc->compname)+", "+trim(calc->compadd)+", "+calc->compcistzip

gives: company, 123 main st, anywhere, state, 33333

[Marilyn Wallace]

That did it! Thanks!

Your solution works beautifully.

Thank you very much, Dave.

Marilyn Wallace

[jmatienza]

More details on this please?

If you really must combime all in a calculated field,

you can do a lookup on the name in an expression and add it to another lookup and add it to the third lookup. All in one calculated field.
Example:

trim(lookup(name,,,))+", "+trim(lookup(city,,,))+", "+Lookup(state,,,)

does this make sense?

Dave

Hi, all. How does one carry this out? I entered a variant of the above expression in the the calculated field-type field rule of my table. I didn't want a filter parameter, so I entered an empty "" after the first comma. But when I viewed my form (the field in question is in an embedded browse), nothing happens.

[Stan Mathews]

Re: More details on this please?

Hi, all. How does one carry this out? I entered a variant of the above expression in the the calculated field-type field rule of my table. I didn't want a filter parameter, so I entered an empty "" after the first comma. But when I viewed my form (the field in question is in an embedded browse), nothing happens.

A calculated field is calculated during new record entry, when one of the fields involved in the calculation is modified, or when you invoke one of the recalculation methods.

[jmatienza]

Hi, Stan! Pardon my thickness, but I don't quite follow you. I want to do what Dave wrote:

...you can do a lookup on the name in an expression and add it to another lookup and add it to the third lookup. All in one calculated field.

I entered the calculated field expression as suggested, but no lookup occurrred. I realize I'm missing something, since the expression does not accomplish a lookup at all, but is merely a string expression containing several lookup values. The question is, How should I supply the lookup values? I can't make a lookup field rule for a calculated field type.

[DaveM]

Re: A calculated fill-in from a lookup table?

The calculated fields I showed you are on a report. I have used the same scenario for a header on a form. This calculation method is a little strong for a form where you may want to change the data somehow since calc fields cannot be used for data entry or editing.

I would not combine the fields into another table. It is redundancy and I can always make the necessary calcs to combine later as needed. If I do a lot of this, a query in a udf would make much better sense.

Dave Mason

[jmatienza]

Re: A calculated fill-in from a lookup table?

The calculated fields I showed you are on a report. I have used the same scenario for a header on a form. This calculation method is a little strong for a form where you may want to change the data somehow since calc fields cannot be used for data entry or editing.

Hi, Dave! Thanks for replying. I didn't realize you were talking about calculated fields on a report. What I wanted was a field rule at the table level that would combine fields from a table lookup into a single field.

I would not combine the fields into another table. It is redundancy and I can always make the necessary calcs to combine later as needed. If I do a lot of this, a query in a udf would make much better sense.

That makes sense to me, Dave. I'll take your advice on that. For that matter, it might be even better to just put in a foreign key and make a child table out of the lookup table, instead of duplicating the fields.

But, just out of curiosity, how would you make a field rule like I described? Is it possible?

Thanks in advance.

[Tom Cone Jr]

Re: A calculated fill-in from a lookup table?

Jim,

I don't think you can define the table lookup field rule to autofill a table field with a calculated result obtained from the lookup table. The suggestions at the top of this thread contemplate autofilling fields, and then using a separate calc field rule to compute the calculated result and store it in yet another field in the same table. For good reasons you're after something a bit different.

The result you want to achieve could be obtained by defining your own custom lookup routine in the data entry form, rather than by using Alpha's built in field rules. You could supply a button which pops up a list from the "lookup" table and then the button's onPush event script could compute the desired result and assign it to a field in the current table.

[DaveM]

Re: A calculated fill-in from a lookup table?

Kinda like tom said?.?.

I would put the new field(hidden) on a form. As the data is entered and upon leaving the last entry field(or on save) trap the fields needed into variables, concatenate them like I want them and then set the hidden field with that data, which would be a variable. You could then do your lookup from field rules using only the one field. It would mean a large field in each table to save that data, but it would work.

There is also calculated fields for tables and sets now. Just an idea!

I am old school and remember when space on disk(sometimes a floppy disk) was of the utmost importance. Most people today don't remember ram drives and how we scrambled to get code to fit within memory, or how long it took to make a small .exe(I ate lunch while the computer churned. The only redundancy we could allow ourselves was the necessary fields to connect 2 or more tables.

Dave Mason
www.lotrun.com

[jmatienza]

Re: A calculated fill-in from a lookup table?

Jim,
I don't think you can define the table lookup field rule to autofill a table field with a calculated result obtained from the lookup table.

Hello, Tom! Coming from you, I'll take that as the definitive answer.

The result you want to achieve could be obtained by defining your own custom lookup routine in the data entry form, rather than by using Alpha's built in field rules. You could supply a button which pops up a list from the "lookup" table and then the button's onPush event script could compute the desired result and assign it to a field in the current table.

I never thought of that. I'll give it a try. Instead of a button, though, I'll have the lookup table pop up when the field gets focus.

Thank you very much for clearing that up.

[jmatienza]

Re: A calculated fill-in from a lookup table?

Kinda like tom said?.?.

I would put the new field(hidden) on a form. As the data is entered and upon leaving the last entry field(or on save) trap the fields needed into variables, concatenate them like I want them and then set the hidden field with that data, which would be a variable. You could then do your lookup from field rules using only the one field. It would mean a large field in each table to save that data, but it would work.

There is also calculated fields for tables and sets now. Just an idea!

I am old school and remember when space on disk(sometimes a floppy disk) was of the utmost importance. Most people today don't remember ram drives and how we scrambled to get code to fit within memory, or how long it took to make a small .exe(I ate lunch while the computer churned. The only redundancy we could allow ourselves was the necessary fields to connect 2 or more tables.

Dave Mason
www.lotrun.com

Oh, hi, Dave! I didn't see your post when I came in! Thanks for the suggestion. I thought about how I could do something like that, but I'll go with Tom's suggestion for now, since it requires less fields.

I beginning to really appreciate what an asset this message board is. Thanks, all!