Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

[lsummy]

Hi all, I would appreciate some commentary on an issue I've run into with Repeating Sections.

I have a database design which uses a very similar format to that of a typical order header table + order lines table format. In this simple case, I have been able to follow the available tutorials and commentary to make this scenario functional in a UX component using the repeating section. The system allows me to create a form which is bound to both the header and line items, fill in information, and submit new data to both tables using the built-in action scripting. All seems to work well.

My scenario, however, also has an additional table (extra data, let's say) which has a one to one relationship to the order lines in the above example. Alpha allows me to link (one-to-one) the extra data table nested under the the one-to-many link of the order lines in the data binding dialog, and also allows the creation of data bound controls. When generating the SQL insert statements, it appears that Alpha overlooks the fact that the extra data table even exists. The header record and multiple line item records are inserted, but the extra data is ignored. Consequently, when the form is refreshed the controls bound to the extra data table are cleared because the data was never inserted.

I have checked the SQL statements being executed, and it appears the extra data is not even considered. I ran a debug statement, and the extra data is present in the form submission.

Further, I manually created some record entries to simulate a proper set of data. When viewed for edit in the same UX, the extra data record is present and properly represented. I am also able to change data in the form and successfully submit the data to the database for update.

I am willing to produce a test project for review, or provide more information, but I was hoping someone would recognize this scenario and tell me I forgot to tighten the muffler bearing clamp. :)

I am running build 2999-4519. All testing has been done in the developer environment.
I have also tested this in the latest pre-release build.

Thank you in advance for any suggestions.

[lgrupido]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

I haven't done a structure like that before, but here are some things to think about:

How is the PK of the invoice child record (1st level) created?

My guess as to what is happening: When you insert the main invoice record, it generates a PK. That PK is then used to help create the child records. Those child records are then created and each child record has a PK. But Alpha doesn't do a third round of inserts, so far as I know. So if the PK of the child records isn't known up front, Alpha may not be able to create the third set.

If the PK of the third set was already known at the start (for instance, if the PK of the invoice line item was simply Invoice_num + line_product_id then Alpha would know the PK ahead of time and create the extra records also - but if it has to wait to find the PK, it may get lost.

Don't know if this is a bug or a limitation, if so - but something to look into.

For a test: Try creating a PK for the line records and the third table based solely on data except for the invoice_num and see if it works correctly.

If so, I would send the result to Alpha as a Bug and see what they have to say about it.

[Davidk]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

Logan, if you could provide a sample UX and tables that would help. When I've needed to go outside a normal process I've do so in code.

[peteconway]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

If you are referencing the PK anywhere in the child table (as bound) - try removing it as Alpha references it to commit the record, if you do it as well the SQL insert will fail.

[lsummy]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

Thank you all for your responses. I have finished up an additional round of troubleshooting and have a couple conclusions. I will post what I found soon.

[lsummy]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

I have created a test database, project, and video to help illustrate the issue I'm dealing with. It seems like a bug, but I am also willing to accept the possibility of developer error. I'm still pretty new with Alpha so I wouldn't be surprised if I missed a setting somewhere.

Here is a dropbox link to the mySQL model, and a ZIP of the Alpha project in the video.

Here is a link to the video. Please note, I rushed myself a bit at the very end and forgot to type some text in the extra data field. You can still see a record was not inserted because the Extra Id field was not refreshed with any data.

Until I can get a solid answer on this topic, I have been able to create a workaround which requires manual creation of xbasic code. I will try to find some time this week to post an explanation.

Please let me know if any further information or clarification is required.

[peteconway]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

This may be an issue.
2015-11-30_2108.png

[GGandhi]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

i don't know whether it is a bug or not.
since these data are kept in memory and updated the primary key value of the child table is not available till the records are updated. since that is the linking field and is not available the grand child table is not updated even if properly data bound.
the easy method will be to get the primary key of the child record and insert the data into the grand child table via xbasic .
take a look at your video at 3:24 where you show the bindings for repeating section, the line item shows, not the extradata.
so as far as the ux/dialog concerned extradata does not exist even if bound properly in the definition.

[GGandhi]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

if you do not want to hand code insertion to the third table ( grand child ) i think this needs to be done in three updatable grids.
parent updatable grid with row expander.
the row expander will hold the child grid with a row expander.
that row expander will hold the grand child grid.
since all the keys will inserted and available this should work. there were no need for me to do structure like that before so this is untested.

[lsummy]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

If you are referencing the PK anywhere in the child table (as bound) - try removing it as Alpha references it to commit the record, if you do it as well the SQL insert will fail.

My working project does have controls inserted into the form which reference the PKs, but they are set as non-updateable. If I understand non-updateable correctly, alpha will display bound information but will skip those fields when creating any SQL statements. To help eliminate this variable, I removed the bound controls to the PKs altogether, and the results were the same as shown in this video.

[lsummy]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

This may be an issue.
[ATTACH]41341[/ATTACH]

I did try setting updatable to yes, and experienced the same results.

The SQL database is using Auto Increment INTs for the PK. It is my opinion that the non-updateable status should be set because the client should never attempt to update the PK field when it is managed directly by the database. It appears a bound control reference to the PK is not required at all in this case, and was only included for convenience. Given that, I completely removed the controls you are questioning and the results were the same as before. Thanks for the comment.

[peteconway]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

Sorry just looked at this with fresh eyes this morning - Selwyn once told me (years ago) you can't one-to-one on a third level of binding, you could get the same result using three lists (bound) each being a child of each other - lists work better in mobile.

[Selwyn Rabins]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

when you have one-to-one links, the child tables are considered LOOKUP tables. you cannot enter new records into them. you need to have separate code to add records to the lookup tables.

for example, imagine you have "order_details" linked one-to-one to the "products" table.

in the order_details table you might have field called "product_id" that links to the "products" table.

if the user enters a value into the "product_id" field in "order_details" that does not match an EXISTING record in the "products" table, that should be considered an error. You could add code to your component to allow the user to add a new record to the "products" table and then the user would enter the "product_id" of the newly entered "products" record into the "product_id" field in the "order_details" records.

put another way, when you have one-to-one links you can only do INSERTS into the top-most parent table of the one-to-one linked tables.

[lsummy]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

when you have one-to-one links, the child tables are considered LOOKUP tables. you cannot enter new records into them. you need to have separate code to add records to the lookup tables.

for example, imagine you have "order_details" linked one-to-one to the "products" table.

in the order_details table you might have field called "product_id" that links to the "products" table.

if the user enters a value into the "product_id" field in "order_details" that does not match an EXISTING record in the "products" table, that should be considered an error. You could add code to your component to allow the user to add a new record to the "products" table and then the user would enter the "product_id" of the newly entered "products" record into the "product_id" field in the "order_details" records.

put another way, when you have one-to-one links you can only do INSERTS into the top-most parent table of the one-to-one linked tables.

Thanks for taking the time to comment on this Selwyn. It looks like my application is a bit unusual, and I have tried to use this one-to-one link in a way that is outside the design intent. In my case, the one-to-one link is supposed to be transparently inserted right after its parent is created. That relationship is permanent, and never changes. It’s kind of like meta data for a record being stored in a separate table. I can, and have made this work in code using the ‘AfterRecordInsert’ event in the action scripting settings for ‘Save Submitted Data To Tables’.

One related question, UPDATING fields in a one-to-one linked record does seem to work okay. I assume this is the case since the relationship is already established, and a pre-existing record is there to update in the first place. I just want to be certain the ability to automatically update fields in a one-to-one linked record is intended behavior. The fact that the system allowed the update of a one-to-one linked record is what led me down the road to thinking the system should have also been inserting a new record also. This would make sense for my design case, but possibly not for the lookup scenario you describe above.

It appears there are some alternate design approaches I can take. (One being a change in database architecture.) The database design was actually carried over from a different system, so my goal was to see if I could fit it into the Alpha Anywhere design philosophy without making changes. Changes are okay in this case.

Thank you everyone for your time and comments.

[Selwyn Rabins]

Re: Repeating Section With Nested One-to-one Link Fails to Insert Linked Record

yes, you are correct. you can UPDATE a record in a child one-to-one relationship because the parent record establishes the relationship to an existing record in the child table and so the update is allowed.