Alpha Video Training
Results 1 to 11 of 11

Thread: Problems with Referntial Integrity

  1. #1
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    732

    Default Problems with Referntial Integrity

    Trying to create a one_to_many set with two links to the same table;
    Parent is 'Appendix_c' while child is 'Logging'
    my links are:
    appendix_c->unique = logging->op_unique
    appendix_c->base_unique = logging->base_unique
    A5 happily did all this with no errors, I then tried to set 'Referential Integrity' to 'cascade changes/deletes'
    and saved the set.

    When I try and open a form on this set I get the following message twice, presumably for each link.

    Error loading test_fleets.set
    Error adding relation child 'Logging'; for integrity, use parent field name
    cannot work out what A5 is trying to tell me!!

    any suggestions welcomed
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  2. #2
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,584

    Default Re: Problems with Referntial Integrity

    Alpha should have assigned an alias to the 2nd instance of the same table. If you tried to set RI to child both tables that is probably why you are getting the error. Other than that it sounds like the field you are linking on is probably wrong.

    So why the 2 instances of the same table?
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  3. #3
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    732

    Default Re: Problems with Referntial Integrity

    Quote Originally Posted by MoGrace View Post
    Alpha should have assigned an alias to the 2nd instance of the same table. If you tried to set RI to child both tables that is probably why you are getting the error. Other than that it sounds like the field you are linking on is probably wrong.

    So why the 2 instances of the same table?
    maybe I explained it wrong, I am trying to link the two tables using two fields rather than two links. I have used one-to-many sets for years but have never been able to get the Referential Integrity part to work.
    appendix_c->Unique is a underlying unique number for a field 'Operator' which contains name of an aircraft operator, which can be changed, but not the unique
    appendix_c->base_unique is also an underlying unique for the 'Home base' of the aircraft used by the 'Operator'.
    When I change the appendix_c->base_unique (.ie the operator moves to a different location), I also need to change the 'Home Base' in the logging->base_unique field, to keep the operators fleet in sync.
    I've 'sort-of' got it to work by only having one link on appendix-c->unique/logging->op_unique and then attaching the following script to the 'ON_CHANGE' event of the appendix_c->base_unique field:
    t = table.open("logging")
    query.filter = "op_unique = "-quote(Appendix_C->Unique)
    query.order = "recno()"
    t.query_create()
    t.fetch_first()
    while .not. t.fetch_eof()
    t.change_begin()
    t.base_unique = appendix_c->Base_unique
    t.change_end(.t.)
    t.fetch_next()
    end while
    topparent:Control_browse1.refresh()
    two problems arise:
    1) I sometimes get record locking problems
    2) If I change my mind and cancel the change in appendix_c->base_unique the script has already changed logging->base_unique so the two tables become un-synched.
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

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

    Default Re: Problems with Referntial Integrity

    Can you simplify the table and set construction and repost please Graham?
    If the operator number and home base are in the same table there should not be an issue.
    Your description of the problem is a little confusing, to me anyway.
    Ted Giles
    Example Consulting - UK
    .

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

  5. #5
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    732

    Default Re: Problems with Referntial Integrity

    Quote Originally Posted by Ted Giles View Post
    Can you simplify the table and set construction and repost please Graham?
    If the operator number and home base are in the same table there should not be an issue.
    Your description of the problem is a little confusing, to me anyway.
    Attached Files Attached Files
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  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,271

    Default Re: Problems with Referntial Integrity

    Not sure what you are trying to achieve. The uploaded example failed due to unrecognised field in the set browse.

    I have tried to understand what you need but failed.

    Can you write it in simple format like pseudo code?
    What needs changing and when would be good.
    Ted Giles
    Example Consulting - UK
    .

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

  7. #7
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    732

    Default Re: Problems with Referntial Integrity

    I have three main tables:

    1) Appendix_C (Test_operator_name in example)
    Data on Aircraft Operators, name, home base, insignia, nicknames etc

    2) Base_Details (Test_Base Details )
    Data on Airfields, Name, placename, airfield information etc

    3) Logging (Test Logging)
    Data on Aircraft, Type, name location , owner/operator etc

    plus many small tables containing all the look-up values for use in the big three.

    One-to-many set Test_Fleets has Test_operator_name as Parent and Test_Logging as child. with the link on Test_operator_name->Unique to Test_Logging->op_unique.
    The problem occurs when I need to change the Parent (Test_operator_name->base_unique) value as I also need to change Test_logging->base_unique to the same value for the child records so that Test_logging entries are still in sync.

    I was hoping referential Integrity would do the trick by using both test_operator_name->unique/test_logging->op_unique AND test_operator_name->base_unique/test_logging->base unique as the link between Parent and child, but A5 doesnt like it.

    I have got around this by adding the following script to the ONCHANGE event on Test_operator_name->base_unique:

    t = table.open("test_logging")
    query.filter = "op_unique = "-quote(TEST_OPERATOR_NAME->Unique)
    query.order = "recno()"
    t.query_create()
    t.fetch_first()
    while .not. t.fetch_eof()
    t.change_begin()
    t.base_unique = TEST_OPERATOR_NAME->Base_unique
    t.change_end(.t.)
    t.fetch_next()
    end while
    topparent:browse1.refresh()
    this works most of the time, but two problems occur:

    1) record locks
    2) If I cancel the change, it does not change the child records back.

    Corrected zip attached
    Attached Files Attached Files
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  8. #8
    "Certified" Alphaholic
    Real Name
    John Koh
    Join Date
    Jan 2004
    Location
    Maryland, USA
    Posts
    1,062

    Default Re: Problems with Referntial Integrity

    Graham,

    The code is missing "table.close()" and "qry.drop()" in your code and i could not find where did you apply it in your sample?

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

    Default Re: Problems with Referntial Integrity

    That is an issue with the code, certainly.
    However, I suspect it's more to do with the Set design.
    I'm working today but was planning to revisit later and try a different Set configuration.
    Simplifying the PK and/or linking field name with standardisation could also enable a 1 to 3 and 3 to 2 (above) Set.
    Ted Giles
    Example Consulting - UK
    .

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

  10. #10
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    732

    Default Re: Problems with Referntial Integrity

    Quote Originally Posted by Ted Giles View Post
    That is an issue with the code, certainly.
    However, I suspect it's more to do with the Set design.
    I'm working today but was planning to revisit later and try a different Set configuration.
    Simplifying the PK and/or linking field name with standardisation could also enable a 1 to 3 and 3 to 2 (above) Set.
    Thanks for your help Ted,

    abandoned use of RI as it was causing more problems than it solved. I settled on the following code, attached to the ONCHANGE event on the base_unique field on my parent form.

    t = table.get("logging")
    query.filter = "op_unique = "-quote(Appendix_C->Unique)
    query.order = "recno()"
    qry = t.query_create()
    t.fetch_first()
    while .not. t.fetch_eof()
    t.change_begin()
    t.base_unique = appendix_c->Base_unique
    t.change_end(.t.)
    t.fetch_next()
    end while
    qry.drop()
    seems to do what I want it to do!
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

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

    Default Re: Problems with Referntial Integrity

    Glad it's working Graham.
    I spent a while earlier today and it is a bit of a poser.
    I tried different setting in a set and different configurations and got closer.
    I'll take this off line as a "challenge" as it should be possible.
    Will post and PM you if I get it working on my box.
    Ted Giles
    Example Consulting - UK
    .

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

Similar Threads

  1. Referential integrity in 1:1 set.
    By CALocklin in forum Alpha Five Version 10 - Desktop Applications
    Replies: 11
    Last Post: 06-01-2011, 05:54 PM
  2. Integrity Error
    By William Crews in forum Alpha Five Version 5
    Replies: 1
    Last Post: 09-05-2003, 03:46 AM
  3. Referential Integrity
    By Ray in forum Alpha Five Version 5
    Replies: 0
    Last Post: 04-09-2003, 02:32 PM
  4. Sets - Integrity
    By Charles Hoens in forum Alpha Five Version 5
    Replies: 8
    Last Post: 12-30-2002, 03:58 AM
  5. Referential Integrity
    By James Peterson in forum Alpha Five Version 4
    Replies: 3
    Last Post: 10-22-2001, 06:04 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
  •