PDA

View Full Version : Data Sets


ABC123

Selwyn Oster
03-21-2014, 07:44 AM
I have a primary table that links to a secondary table on a one-to-many link.
All columns to link to are contained in the primary table, with the exception of the first column in the secondary table which contains a 3-char record type ("SYM").

How do I create the link?

Tom Cone Jr
03-21-2014, 08:49 AM
Hi, Selwyn.

Are you working with native Alpha Five tables?

If so, typically the values used to link records between the two tables must be present in both tables. In your case a value you want to use as a link is present in only one table. Can that value be "computed" or "derived" from other fields in the primary table?

Selwyn Oster
03-21-2014, 09:06 AM
Hi Tom

I am working with MySQL tables.

At present there is no data on the primary record to compute or derive the field value to be used to link to the secondary record.

However the record type for the secondary table can be calculated or derived as all the records in the secondary table have the same record type.
E.g field name SymGroup. The first 3-characters for every record in its table has a record type of "SYM".

If this cannot be resolved it is possible to add an additional column to the primary record containing "SYM" and pass this column in the linkage. Here I would appreciate some assistance to set this value dynamically in the new primary column prior to the link taking place. Then columns could be used in the linkage.

I note that there is also provision for an expression to use in the linking. However I can't find any documentation on the use of the expression in a multi-column link.

Tx
Selwyn

Tom Cone Jr
03-21-2014, 10:02 AM
Perhaps others will be able to assist. I have no experience with mySQL.

Selwyn Oster
03-21-2014, 10:06 AM
Thanks anyway.
How would you do this assuming the files were native alpha dbf?
Selwyn

Ted Giles
03-22-2014, 06:36 AM
There are a number of ways.
Easiest is to have the linking field in both tables.
Table 1 SYM matches Table 2 SYM
You can link on multiple fields in the same manner as long as they are present in both.
The field names do not have to be the same, but the Data Type and Length does.

Next is with Expressions.
Suppose you want to link on Name and Number and Date
Then the expression could be Name+str(Number)+dtoc(Date) which would give you "Billy123401/01/1900"
You can easily test your expressions in the Interactive Window like this;
? dtoc(date()) and see what you get.

If you cannot derive a logical link, then the last option I would go for is the DUMMY field.
A One char field in both tables and link on that. It doesn't have anything in it, so both tables will be linked completely.

Ray in Capetown
03-22-2014, 06:59 AM
If I get this right

1. You suggest to prepopulate a new field in primary table with the text "SYM" and include this in the linkage. You can
2. Do this dynamically - on what basis if the link does not yet exist?
If it is simply based on the first three letters of text of the child table then you could use something like newtypefield=left(childfield,3) as your link expression

Selwyn Oster
03-23-2014, 12:33 AM
Sounds good.
I have a composite key of several columns.
So "SYM"+ column2+ column3... in the parent and SymRecType+column2+ column3+... in the child would work?

Selwyn.

Selwyn Oster
03-23-2014, 12:36 AM
Thanks for your detailed explanations.

I will try them and let you know.

See also my response to Ray below.

Regards,

Selwyn

Ray in Capetown
03-23-2014, 04:02 AM
Sure. That expression would establish a link for child records where symRecTyp contains "SYM" AND the field is only 3 char wide AND column2 and column3 match.