New call-to-action
Results 1 to 10 of 10

Thread: Data Sets

  1. #1
    Member
    Real Name
    Selwyn Oster
    Join Date
    Feb 2011
    Location
    South Africa
    Posts
    31

    Default Data Sets

    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?

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,320

    Default Re: Data Sets

    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?

  3. #3
    Member
    Real Name
    Selwyn Oster
    Join Date
    Feb 2011
    Location
    South Africa
    Posts
    31

    Default Re: Data Sets

    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

  4. #4
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,320

    Default Re: Data Sets

    Perhaps others will be able to assist. I have no experience with mySQL.

  5. #5
    Member
    Real Name
    Selwyn Oster
    Join Date
    Feb 2011
    Location
    South Africa
    Posts
    31

    Default Re: Data Sets

    Thanks anyway.
    How would you do this assuming the files were native alpha dbf?
    Selwyn
    Last edited by Selwyn Oster; 03-21-2014 at 09:38 AM. Reason: Further question

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,493

    Default Re: Data Sets

    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.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  7. #7
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Data Sets

    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

  8. #8
    Member
    Real Name
    Selwyn Oster
    Join Date
    Feb 2011
    Location
    South Africa
    Posts
    31

    Default Re: Data Sets

    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.

  9. #9
    Member
    Real Name
    Selwyn Oster
    Join Date
    Feb 2011
    Location
    South Africa
    Posts
    31

    Default Re: Data Sets

    Thanks for your detailed explanations.

    I will try them and let you know.

    See also my response to Ray below.

    Regards,

    Selwyn

  10. #10
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Data Sets

    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.

Similar Threads

  1. How best to move large data sets between tables?
    By parkjammer in forum Mobile & Browser Applications
    Replies: 6
    Last Post: 11-20-2013, 12:20 PM
  2. Multiple sets of data files
    By mfriesen in forum Alpha Five Version 11 - Desktop Applications
    Replies: 15
    Last Post: 04-10-2013, 02:06 PM
  3. Multiple data sets, one application
    By Robin Hoare in forum Alpha Five Version 7
    Replies: 5
    Last Post: 12-29-2005, 05:53 PM
  4. A thought about the issue of multiple data sets ..
    By drgarytraub in forum Alpha Five Version 5
    Replies: 10
    Last Post: 12-08-2003, 06:35 AM
  5. Multiple Data Sets
    By harold bussell in forum Alpha Five Version 5
    Replies: 1
    Last Post: 10-30-2003, 03:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •