creating a conditional link for a set

[Vince McGinnis]

This question may have been asked before, but I didn't find it when I did a search.

I can't figure a good way to link these two tables.

I have a one to many set.

Parent table link field: PNIIN

Child table has two possible link fields: NIIN and ALT_NIIN

either one of these two child fields could have the linking NIIN data needed.

I know...this data does not follow rules of normalization, but it's not my table and I'm not allowed to change it.

Question: How do I create a link between the two tables so that I can see all child records where:

PNIIN = NIIN
... or ...
PNIIN = ALT_NIIN

Many thanks for any assistance!

Vince

[Stan Mathews]

RE: creating a conditional link for a set

PNIIN -linked to - if(isblank("NIIN"),ALT_NIIN,NIIN)

[Vince McGinnis]

RE: creating a conditional link for a set

Stan,

Thanks for the quick reply!

That's basically the link expression I'm currently using.

The problem I'm having is that sometimes both of the child NIIN fields are filled in.

I need a way for Alpha Five to look at both child fields, even if both are filled, and link to the one that matches the parent NIIN field.

I can't figure out the logic.

Any suggesions?

Thanks again.

[Tom Cone Jr]

RE: creating a conditional link for a set

Vince, this isn't responsive to your question, but the need for a 'conditional' link in your set may be affected...

If the NIIN and ALT_NIIN fields mean different things I'd use two different sets altogether. If they mean the same thing, but perhaps come from different sources, then I'd scrub the data to reduce this to a single field in the table. In this latter instance as long as they are both present in the table the user could potentially update the wrong one later on... (my experience suggests that they *will* update the wrong one later on...)

-- tom

[Stan Mathews]

RE: creating a conditional link for a set

Tom's advice is sound. If you have to proceed in the current manner, as you suggest, try:

PNIIN - linked to - case(isblank("NIIN"),ALT_NIIN,isblank("ALT_NIIN"),NIIN,PNIIN=NIIN,NIIN,PNIIN=ALT_NIIN,ALT_NIIN)

Haven't tested this and it seems counter-intuitive, but worth a shot.

[Vince McGinnis]

RE: creating a conditional link for a set

Tom,

Thanks for your help. I'm using the child table for reference purposes only (read-only). No data entry involved, just checking the validity of the parent table data based on data in the linked child table.

When I try writing an expression for the child link, Alpha Five doesn't see the fields in the Parent table. This makes it difficult to write an expression to compare the Parent and Child link fields.

I've been trying to get Alpha Five to compare the Parent's PNIIN field with each of the child NIIN fields by assigning the PNIIN field value to a variable and comparing the variable to child link fields, but no luck.

In a previous message, someone mentioned they used variables to link 1 to N tables. But I sure haven't had any success using variables to make the link.

[Vince McGinnis]

RE: creating a conditional link for a set

Stan,

Will give it a try. But as I mentioned to Tom, I don't seem to have any visability of the Parent table when writing the Child table link expression.

What gives??

Thanks,

[Tom Cone Jr]

RE: creating a conditional link for a set

Vince,

To establish a new baseline, have you considered defining a new database operation? You could 'mark' all the parent table records where the key field matches neither of the two fields in the second table. Then the user could edit the marked records to fix the problems.

Thereafter as data entry occurs you could use a custom script to check a new record's value against 'both' possible values in the second table.

Would this help?

-- tom

[Vince McGinnis]

RE: creating a conditional link for a set

Tom,

Since I'm using the child table to check the validity of data in the Parent table I have considered using two subtract operations to find inconsistancies:

A Parent table to Child table subtract operation to find possible deletes
A Child table to Parent table subtract operation to find possible adds.

That may be what I end up doing, but even subtract operations require a link between the tables.

I may need to create an intermediate table.

Thanks

[JohnZaleski]

RE: creating a conditional link for a set

Vince,
I don't think there is any way to have a set that links to your child that will present all possible hits in a singls browse ( Hope I'm wrong ). You could easily do it if it was okay to show the child records in two browses: Create a set with two one to many's:
parent (pnin) to child(niin)
parent (pnin) to child(alt_nin)

Other solutions would require an extra table possibly having your child record keep the niin and alt_nin as its own child record. It might help to brainstorm if you gave a brief real world description of what you are trying to accomplish. Is it some kind of substitute or alias function for an inventory or a bill of material app?
John

[Vince McGinnis]

RE: creating a conditional link for a set

John,

Yes, it's basically a bill of materials for maintenance procedures. The NIIN of course, being the unique identifier for the tool or material.

The alternate NIIN is really a corrected NIIN, sometimes wrong NIINs get put on the maintenance procedure, or the NIIN has been superceded by a more current one.

I'll probably end up creating script to copy out those fields to another table, a intermediate table to link the two tables. Unless someone comes up with a better idea.

Thanks,

Vince

[Stan Mathews]

RE: creating a conditional link for a set

"The alternate NIIN is really a corrected NIIN, sometimes wrong NIINs get put on the maintenance procedure, or the
NIIN has been superceded by a more current one."

So if there is an "ALT_NIIN" it is to be used, otherwise use the "NIIN".

PNIIN -linked to - if(isblank("ALT_NIIN"),NIIN,ALT_NIIN)

[Vince McGinnis]

RE: creating a conditional link for a set

Stan,

The child table may have a corrected NIIN but the parent table may still have the old NIIN.

In which case I have to link to the old NIIN to have visability of the new NIIN, so I can then decide if I need to make corrections to the parent table.

I did get everything to work by creating an intermediate table.

I just wish Alpha would give me visability of the parent table when trying to create a child table link expression, I could have done the conditional link there.

Thanks to you and all who assisted in this thread.

Vince

[JohnZaleski]

RE: creating a conditional link for a set

Vince,
If I understood everything. this seems to be fairly close to doing it.

[JohnZaleski]

RE: creating a conditional link for a set

Sorry
Slight Correction to the above is attached