Multi-column link not writing link fields in child

[Lawrence Fox]

I have a set with several tables in it. One of the child tables is linked through several columns (fields), e.g., the link is defined as:

ALLTRIM(ERNO)+ALLTRIM(DIVNO)+ALLTRIM(BENEFIT_CODE)+ALLTRIM(EE_CLASS)

The child table is on the "many" side of the 1:M relationship. When I enter records directly into the resulting browse, none of the link fields are populated with any values, so records are being created in the child, but don't show in the browse. If I make the link fields required in the child table, I get an error message that a required field isn't filled.

I don't want to create a "drill-down" form to add records to this browse as I've done with others--it's a relatively simple set of records and it makes sense to allow direct entry to the browse's records by data-entry people.

What have I done wroing here? Is there a secret that I'm missing :)

[Tom Cone Jr]

Lawrence, a simple search for multi-field link turned up this thread which appears to offer a good idea for you:

http://msgboard.alphasoftware.com/al...lti-field+link

Hope this helps. Let us know.

-- tom

[Edward Larrabee]

It is not a good idea to trim values that you are using in an index expression. The results will be unpredictable if the data trims to different lengths.

[Lawrence Fox]

Tom:

There's an article at LearningAlpha.com that discussed using a concatenated field as the link instead of doing it "on-the-fly" as part of the set definition, but I had a problem with it--I'm not at my office right now so I can't check until later today--I think I got an error message that a calcuated field couldn't be used as the link field for a set. I'll reread it again tonight and try again.

Edward:

Hmm...there were a couple of posts here last night that I was looking at that suggested the opposite--that all indexes and concatenated fields should use alltrim() to eliminate spaces within the key. Are you saying that is NOT the preferred method and that NONE of my indices or set linkages should use alltrim() in order to ensure consistent lengths within the index or link?

What sort of inconsistent results? Queries that don't make sense? Inability to "parse" the concatenated link into its component parts?

[Tom Cone Jr]

What sort of inconsistent results?

Lawrence, perhaps "inconsistent" is a poor choice of words. The difference between trimming field values when concatenating index keys can be illustrated like this.

Assume two fields, Lastname and Firstname.
Assume you want to build an index using a concatenated key
Assume field values like this:

ADAM TOM
ADAM SMITTY
ADAMS LARRY

If you do Lastname + Firstname you get:

ADAM SMITTY
ADAM TOM
ADAMS LARRY

but if you do trim(Lastname) + trim(Firstname) you get:

ADAMSLARRY
ADAMSMITTY
ADAMTOM

Not the same result. This isn't surprising. Perhaps "different results" would be a better descriptor than "inconsistent results". If you concatenate trimmed fields you cannot be assured the resulting list will be sorted first by the field values in the first field, then second by the field values in the second field. Instead, you get the list sorted by the concatenated AND TRIMMED field values. This isn't wrong. It's just different.

-- tom

[Lawrence Fox]

Tom:

Thanks for the explanation re: concatentated with and without trims. Makes sense now. I think I'll leave them untrimmed for now.

To test the linkage thing, I reprinted the artcile from LearnAlpha.com and I've gone back to some other tables and created a field called "ERNOKey" that's a calculated field with a derivation of ERNO+DivNo. (Erno means "Employer Number", btw).

When I look at the table, there's no data in the field. If I retype the Employer Number, the ERNOKey field derives--and then disappears from the screen. How can one index a field that has no data in it? Or run queries quickly if the link field needs to be created on the fly? Is there a method of ensuring that a calculated field contains real not virtual values?

Would I not be better off creating form-level events that ensured that the field always had "real" values in it and that they were the current values of the ERNO and DivNo fields? I'm a wee bit confused here...

[Tom Cone Jr]

Lawrence, are you mixing data types?

-- tom

[Lawrence Fox]

Tom:

No, I'm not mixing data types.

I spoke too soon :o . I didn't realize that I had to go back to the records that had been created and make a change (like retype the EmployerNumber, which is a charactere field) and then the new fields were calculated and the values stored in the table.

I had thought that they'd been calculated when the new columns were added. My bad. I'll know better for next time :) .

[Lawrence Fox]

OK, now I'm getting frustrated. I go directly into the tables to ensure that the values are there. I can get them to "stick" in the Employer_Benefits table (which is the "many" side) but every time I edit the Employers table, I see the new "key" field and they disappear after saving.

[a few minutes later]

Hmm...if I edit another field (not the EmployerNumber) and save the record, the calculated field recalcs. Is there a "recalc" command that would force this action?

[Tom Cone Jr]

I had thought that they'd been calculated when the new columns were added.

Once the calc field rule is defined you can use the Rules option to Re-evaluate field rules, to do the calc in each record.

-- tom

[Lawrence Fox]

Tom:

Thanks for the pointer on the Rules recalc option. That makes life much easier as I change table stuff around :).

And as for the original problem, the "Linking a Set Using More than One Linking Field" article at www.learn alpha.com did the trick as well.

Much thanks to those who wrote it and posted it!