Alpha DevCon 2018
Results 1 to 17 of 17

Thread: Insert Parent, children and grand children - how to

  1. #1
    Member
    Real Name
    Rick G.
    Join Date
    Jun 2010
    Location
    Austin Tx
    Posts
    10

    Default Insert Parent, children and grand children - how to

    Hi All,
    I not sure I understand how alpha handles this normal issue. I am new to Alpha 5 but have coded for a long time. I have 3 tables, a customer tbl, an address tbl and a contact tbl. The Customer will have an address and the address wil have a contact for that addr (parent child grand child) There will be a default address for a customer for eacj type (billing, shipping etc but there could be multiples of the same type ie.. many locations, same with contacts a default and then many contacts at that address. tbl name and key fields -

    Cust Address Contact
    PK_Cust PK_Addr PK_Cntct
    FK_Cust FK_Addr

    Is there a complete example of how this would work, use a add only form for the Cust then in the afterinsert pop up the children? link?

    Thanks!

  2. #2
    Member
    Real Name
    Rick G.
    Join Date
    Jun 2010
    Location
    Austin Tx
    Posts
    10

    Default Re: Insert Parent, children and grand children - how to

    Sorry the table structures did show right..

    Cust:
    PK_Cust

    Addr:
    PK_Addr
    FK_Cust

    Cntct:
    PK_Cntct
    FK_Addr

    thanks!!

  3. #3
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    733

    Unhappy Re: Insert Parent, children and grand children - how to

    Zeke,
    The short answer is no. I personally floated this several times along with others, not within a single grid or with a linked grid in a single view. You would have to step through linked grids starting with the top parent and then work your way through the child grids. This would mean retrieving the newly created primary key and passing it in a variable to the child records as the linking field.

    You could find many complicated work a rounds. You can do all kinds of brain twisting things in the grid events code. But you can't straight, up add multiple new related records very easily.

    I have asked the Wizard behind the curtain to take a look at this as you say, "normal issue." It is a missing flight in the alpha staircase.

    The product rocks it just has some holidays. If you find a easy solution please pass it in a variable to this child developer.

    Bob

  4. #4
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,428

    Default Re: Insert Parent, children and grand children - how to

    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    733

    Thumbs down Re: Insert Parent, children and grand children - how to

    Doesn't work on an insert only grid. You must first save the initial record then from a browse you can use a row expander to add a related record. Spent a night on that one.
    Tks

  6. #6
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,428

    Default Re: Insert Parent, children and grand children - how to

    add the session id to the parent and only show the data for that session.

    Then you don't need an add(insert) only parent grid...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  7. #7
    Member
    Real Name
    Rick G.
    Join Date
    Jun 2010
    Location
    Austin Tx
    Posts
    10

    Default Re: Insert Parent, children and grand children - how to

    I am new to Alpha and do not know all the in and outs and nomenclature, I do not understand how adding the session Id to the record and then using only the data from that session will allow me to have the auto number key available to the children and then again to the grand children - when a New parent, new child and and a new grand child is built each child would need the parent auto number Primary key for to be stored as foreign key reference.. I tried the link grids and the expander approach neither seemed to work. Just trying to understand the way A5 works better so I may develop sound code.

  8. #8
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    733

    Default Re: Insert Parent, children and grand children - how to

    Zeke,
    From the way I understand it you must recall the last created record in a sql statement and than pass it from parent to child in a session or page variable.

    You can also use java script to change, add and pass data between grids. You can use pointers to the parent or child as well.

    There is a bunch of videos that will get you started.
    Bob

  9. #9
    Member
    Real Name
    Rick G.
    Join Date
    Jun 2010
    Location
    Austin Tx
    Posts
    10

    Default Re: Insert Parent, children and grand children - how to

    That seems easy enough. I will be able to find the customer record based on a unique index of the name. Thanks for the help..

  10. #10
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    733

    Question Re: Insert Parent, children and grand children - how to

    You can also generate a guid or guids which you can than pass between records. This is still a little foggy to me.

    When you say unique index do you mean the auto increment field?
    Tks

  11. #11
    Member
    Real Name
    Rick G.
    Join Date
    Jun 2010
    Location
    Austin Tx
    Posts
    10

    Default Re: Insert Parent, children and grand children - how to

    I will force the user to make the customer name unique. It is a requirement for the application and I can use it to easily retrieve the record in an edit grid. I know it seems silly to also have a auto number field as the primary key as well but the schema is reasonably normalized and works on the PK_tablename1 linked to FK_tablename1 key in a child table all of the table use this approach names can change etc so I won;t use it as a primary key but a look up for a new record will work. It maybe that I change all the auto-number primary and foreign keys to use the guids field approach.. lot of work there as well but may be worth it..

  12. #12
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    733

    Default Re: Insert Parent, children and grand children - how to

    There is a video that demonstrate using a guid as a primary key

  13. #13
    Member
    Real Name
    Norman K
    Join Date
    Mar 2006
    Posts
    277

    Default Re: Insert Parent, children and grand children - how to

    Quote Originally Posted by Zeke_11 View Post
    I am new to Alpha and do not know all the in and outs and nomenclature, I do not understand how adding the session Id to the record and then using only the data from that session will allow me to have the auto number key available to the children and then again to the grand children - when a New parent, new child and and a new grand child is built each child would need the parent auto number Primary key for to be stored as foreign key reference.. I tried the link grids and the expander approach neither seemed to work. Just trying to understand the way A5 works better so I may develop sound code.

    Glad I found this thread, sorry to resurrect it if that's a problem.

    I am trying out Alpha5 for a web version of a Desktop application we sell and like a lot of what I see; HOWEVER, in trying to navigate all the ins and outs of this product, I have literally not gotten such a headache since writing my thesis.

    Here's a simplified version of my situation: I have a master file containing records with an autonumber primary key, connected in a 1-MANY fashion to a "detail" file. Specifically, the master file has key "SYSID" and the detail file has the key "FBH_SYSID" linking back to the master. I need to add a master record and then, BEFORE SAVING IT, add multiple details. Like you, I won't have the value of SYSID until I save it. So what I did (on the Desktop app as well) is do away with the autonumber attribute and have a separate, small "counters" table which I query in the 'On Intiialize New Record" event of the grid, pulling the next key out and incrementing it in one pass. Now, HOW to pass this to the detail (or child) table? One option (the ONLY one I could figure out) was to put it in a session variable. Then, when I add a record to the linked grid holding the detail records I read the session variable in the "OnInitialize.." event for the DETAIL table. That sort of works: when I open the detail form to add a detail record the foreign key field shows the parent key value but when I go to SAVE the detail file the foreign key is saved as 0. There's also the problem of what to do if you're adding details to an EXISTING master record (how do you detect whether you're in Insert mode or not, etc.).

    I think my point is that in order to be able to add a master record and then add details WITHOUT FIRST SAVING THE MASTER RECORD you will need to use a counters table and pass the key in a session variable (I have yet to figure out what a "page" variable is).

    HTH (rather than confusing you).

  14. #14
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    733

    Default Re: Insert Parent, children and grand children - how to

    Norman,

    You can generate the Guid in the calculate event with a simple xbasic function. You then have to pass it around like a hot potato. You can create a variable that passes the grid to a linked grid or detail view. You can use the Java e. functions in the events.

    I have asked a few times in different posts to add some properties to the grid component which would allow a row expander or linked grid to accomplish this.

    I see this as follows:When the user looks at his screen it appears to be a contiguous form, but the form is comprised of 2 or more grids that hand down the primary key to create the necessary relationships.

    There are other work a rounds but you have to be fluent in the various events and combine them with xbasic functions, sql commands, variables and java functions.

    I have not mastered this yet. So keep me posted.
    Tks

  15. #15
    Member
    Real Name
    Norman K
    Join Date
    Mar 2006
    Posts
    277

    Default Re: Insert Parent, children and grand children - how to

    Quote Originally Posted by bob9145 View Post
    Norman,

    You can generate the Guid in the calculate event with a simple xbasic function. You then have to pass it around like a hot potato. You can create a variable that passes the grid to a linked grid or detail view. You can use the Java e. functions in the events.

    I have asked a few times in different posts to add some properties to the grid component which would allow a row expander or linked grid to accomplish this.

    I see this as follows:When the user looks at his screen it appears to be a contiguous form, but the form is comprised of 2 or more grids that hand down the primary key to create the necessary relationships.

    There are other work a rounds but you have to be fluent in the various events and combine them with xbasic functions, sql commands, variables and java functions.

    I have not mastered this yet. So keep me posted.
    Tks
    Thanks Bob.

    Yes, generating a temp (guaranteed-to-be-unique) GUID is probably better than using a counters table because then, should the user cancel out, it won't leave holes.

    The problem I run into is probably one of experience or the inability to dig information out of the references or this board: how to pass info from one component to the other in Javascript events. The only "global" variables I am currently aware of are "session" variables which, from what I can infer, can't be SET within Javascript events (though, if published, can be read). So I'm stopped dead in the water there. If I want to pass info in session variables then I am restricted to the server-side Xbasic events which don't seem to have access to the data in the grid (or detail part). I'm certain there is a way to handle this but I can see my trial period for Alpha5 is going to run out before I actually find a way to get this done. :)

  16. #16
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    733

    Thumbs up Re: Insert Parent, children and grand children - how to

    Norman,
    Alpha is a pretty unique product. I suggest taking advantage of one of their specials. The Rabin's stay on the cutting edge. Even if you use another RAD product it is worth the money just to gain the in site the product gives.

    Tks

  17. #17
    Member
    Real Name
    Mark Simonds
    Join Date
    Jul 2010
    Location
    Oregon
    Posts
    21

    Default Re: Insert Parent, children and grand children - how to

    Quote Originally Posted by bob9145 View Post
    Zeke,
    From the way I understand it you must recall the last created record in a sql statement and than pass it from parent to child in a session or page variable.

    You can also use java script to change, add and pass data between grids. You can use pointers to the parent or child as well.

    There is a bunch of videos that will get you started.
    Bob
    You indicate a "bunch of videos". How do I find them?
    Mark

Similar Threads

  1. Only Parents with Children, Please
    By michaelwpayton in forum Alpha Five Version 9 - Desktop Applications
    Replies: 9
    Last Post: 03-03-2009, 07:06 PM
  2. Copying parent and children
    By Nick Oldfield in forum Alpha Five Version 9 - Desktop Applications
    Replies: 19
    Last Post: 12-05-2008, 11:19 AM
  3. delete children with parent or allow orphans
    By Cheryl Lemire in forum Alpha Five Version 8
    Replies: 8
    Last Post: 12-01-2007, 07:30 PM
  4. Duplicating a "Parent" record complete with "Children"
    By Peterg in forum Alpha Five Version 7
    Replies: 4
    Last Post: 02-23-2006, 09:40 AM
  5. Help with sets and children
    By PhilD in forum Alpha Five Version 5
    Replies: 1
    Last Post: 05-06-2003, 12:39 AM

Posting Permissions

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