how/what do I use to link these two tables?

[Mike Wilson]

table 1: Fire Extinguisher type - field values can contain any combination of A, B and C like A, AB, ABC, AC, BC, etc

table 2: Fire types, values are a single letter A, or B, or C

link table 1 as parent to table 2 as child with 1:M link with some type of contains expression so that if fire extinguisher type is A, only table 2 record A will show, but if fire extinguisher type is ABC, all 3 records in table 2 will show.

This is a concept example, and not the actual usage. The number of records for my usage are far more.

[Al Buchholz]

Re: how/what do I use to link these two tables?

How about a 1:3 linkage?

use an expression and link to the same table 3 times with aliases

substr(fe_type,1,1) to table2_1->type
substr(fe_type,2,1) to table2_2->type
substr(fe_type,3,1) to table2_3->type

or some variation similar to that...

Otherwise, I'd write a function to build a list from table 2 as needed.

[G Gabriel]

Re: how/what do I use to link these two tables?

child_linking_filed $ parent_linking_field

[Mike Wilson]

Re: how/what do I use to link these two tables?

I have come to the conclusion that there is no direct method to accomplish this with a two table set.

Al,
Thanks for the idea! I was able to get myself a working model (example attached) using your strategy. However, the reverse set is not achieveable with this strategy.

child_linking_filed $ parent_linking_field

G,
I thought that, too. Expression builder for the child table in set design doesn't see the parent fields and therefore you cannot get that expression to manifest. Also, the result to that expression is type "L", and wouldn't match the value in the parent

[MikeC]

Re: how/what do I use to link these two tables?

Mike,

Here is a way using just a set of two tables and a calculated field....seems to work as far as I can see.....

[Mike Wilson]

Re: how/what do I use to link these two tables?

Hi Mike,
Yes, but with your way, you have hard coded the values and you don't even need the second table, do you?

Is there a syntax solution to get this to work?

dim fe_type as C="AB"
? iif(len(fe_type)=2,"A" .or. "B","C")
ERROR: Argument is incorrect data type

[MikeC]

Re: how/what do I use to link these two tables?

Mike,
Quite correct--and not knowing your exact situation, it is just a simpler way....now if you have to base it on table data then what you have from Al works.

In your next question----you will have to specify a bit more---the way I read what you have/want is

If there are 2 characters it will be either "A" or "B" and if neither of those it will be "C".

I don't think you meant that! :)


EDIT: Mike, is your child table always going to have non-duplicated data? If so then why not hard code it (even if the child table is to be used as a lookup for other scenarios)?

[G Gabriel]

Re: how/what do I use to link these two tables?

Is there a syntax solution to get this to work?

dim fe_type as C="AB"
? iif(len(fe_type)=2,"A" .or. "B","C")
ERROR: Argument is incorrect data type

All season long I predicted it will be Saints vs Colts as it should be and also knew the real Super Bowel is the game between the Saints and the Vikings as it was. What a game!
So, my mind is really way off syntax and xbasic now and I am pretty sure you could do this with 2 tables by some means or anther, perhaps a global variable but to correct this little snippet of syntax you have, it should be:
if(len(fe_type)=2,"A","C")
1-No need for IFF() here
2-You cannot have in an if() function as a true or a false statement ".OR." .It is not a syntax issue, but rather a logic issue. How is alpha to choose whether to make the outcome "A" or "B" ? Even if the syntax was possible, alpha will choose only one each time, the same one. Unlike Pelosi, alpha does not believe in "disributing the wealth".
3-You could perhaps get around that by using rand_string() in place of "A".or."B" but rand_string() will produce a character between "A" and "Z", you could still get around that by making any random charter other than "A" to be "B" but this makes the odds of hitting "A" 1 in 26. And if you are still with me, you could even these odds by making any character >"N" as "A" else "B":

?if(1=1,"A".or."B","C")
ERROR: Argument is incorrect data type
?if(1=1,if(rand_string(1)>"n","B","A"),"C")
= "B"

This not immediately responsive to the question in the thread but rather to you last post.

[Al Buchholz]

Re: how/what do I use to link these two tables?

Al,
Thanks for the idea! I was able to get myself a working model (example attached) using your strategy. However, the reverse set is not achieveable with this strategy.

I never said it was and you didn't ask for a reverse set.

The strategy changes for the reverse set.

A first thought is to make 2 fields in table 1 that are calculated based on the type field

typeA
typeB
typeC

and put the value of A, B, or C if that character is contained in the combo field.

'Normalize until it hurts - Denormalize until it works.'