Hot Topic (More than 10 Replies) Sorting and reparenting issues (Read 2418 times)
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Sorting and reparenting issues
Nov 11th, 2017 at 3:09am
Print Post Print Post  
The parent database is Orders, the element OrderNumber is the unique identifier.  The subform is named LineItems, and it has a field named ParentOrder that contains the OrderNumber of its parent.  Using natural linking, but in the habit of referencing the parent anyway.  The subform records have a number element named SortNo that is generated automatically when added.  That is the element I want to sort on, ascending order.

Here is the code I am using:
Code
Select All
// Sort Line Item record set
// SBasic codes: Search_AND=0, Search_OR=1, Syntax_QA=2, Syntax_REGEX=3
// vKey = @ResultSetSearch(@FN,"ORDERLINES",0,2."!ParentOrder = OrderNumber")
vOrdersSet = @xResultSetForm("ORDERS")
vLinesSet = @xResultSetForm("ORDERLINES")
xResultSetSort(vLinesSet, "SortNo:-1")
//Reparent line Items to new Order
xResultSetReparent(vOrdersSet, "OrderNumber", vLinesSet)
xResultSetClose("ORDERLINES")
xResultSetClose("ORDERS")
 


I tried the xResultSetSearch and had no luck, so I then tried the xResultSetForm, still not sorting. 

I am using the above code to sort records in a subform. Tried to retrieve subform records where the ParentOrder element on the sub form matches the OrderNumber element on the parent form.  When the xResultSetMatch did not work, I tried using the xResultSetForm.  I am doing this with only one Parent record in the current result set (in the Add Mode) and one-fifteen records in the subform result set.

And finally I want to reparent them because I have changed values in the key elements. 

First time using xResultSet commands, but think this should work without putting into a loop?  Tried using the examples in the manual, but need some assistance here, thanks.
The sorting is definitely not working, And I'm not sure how to test the natural reparenting.

  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Sorting and reparenting issues
Reply #1 - Nov 14th, 2017 at 2:39pm
Print Post Print Post  
How are you determining that sort is not working? You are not doing anything with the XResultSet() to determine the order of records in it. Changing the order in it will not change the order of the records on your form that you are viewing.

Reparent will likely fail due to data protections as you are trying to change the parent on records that you have open both in an XRS and also in your current form. You should never even attempt to do this!!! Reparent needs to have exclusive access to the Parent and Child records. Unless the changing of key values is moving records from one parent to a different parent, there is no need to reparent.

-Ray
  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Sorting and reparenting issues
Reply #2 - Nov 14th, 2017 at 5:34pm
Print Post Print Post  
I forgot to mention that I did not manually add any subform records.  I did an Import operation and the line items are coming in, not sorted as they were in the exported record. 
The SortNo element is a visible field in the subform.  I want to loop thru the subform records to update some fields, but want them in normal sorted order first.

(Shouldn't imported records come in the same original sort order?  There is no way to created a default Sort in Add Mode)
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Sorting and reparenting issues
Reply #3 - Nov 14th, 2017 at 5:53pm
Print Post Print Post  
obfusc88 wrote on Nov 14th, 2017 at 5:34pm:
(Shouldn't imported records come in the same original sort order?  There is no way to created a default Sort in Add Mode)


Imported records come in in the order they are in the file to be imported. Export sorts records when performing an export based on the field order in the export spec. If you want your subrecords to be sorted by a particular field, make sure that field is the first subrecord field in the export and in the import(as they need to match). You should not be using XResultSet() commands to do any of this.

-Ray
  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Sorting and reparenting issues
Reply #4 - Nov 14th, 2017 at 9:30pm
Print Post Print Post  
AHA!

"If you want your subrecords to be sorted by a particular field, make sure that field is the first subrecord field in the export and in the import(as they need to match)"

I missed that in the Help guide (still can't find it).

So, I moved OrderLines!SortNo to position 134, the first line in the list of subform elements being imported.  And I did that same thing in the Import Spec and it worked perfectly.  So, it's not the first element in the Export/Import Specs, but it is the first element in the subform elements of those Specs.

Thank you Ray.  Will have to file this with "special notes".
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Sorting and reparenting issues
Reply #5 - Nov 14th, 2017 at 9:35pm
Print Post Print Post  
obfusc88 wrote on Nov 14th, 2017 at 9:30pm:
I missed that in the Help guide (still can't find it).


Page 344 under the heading "Sorting of Export files"

Quote:
So, it's not the first element in the Export/Import Specs, but it is the first element in the subform elements of those Specs.


Yes it doesn't need to be #1 in the import/export order but it needs to be the first of the subform elements.

-Ray
  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Sorting and reparenting issues
Reply #6 - Nov 14th, 2017 at 9:45pm
Print Post Print Post  
OH, in the User Guide, not in the Programming Guide, got it!

And finally, again:  And I'm not sure how to test the natural reparenting. 

Code
Select All
xResultSetReparent(vOrdersSet, "OrderNumber", vLinesSet)
xResultSetClose("ORDERLINES")
xResultSetClose("ORDERS") 



Is this programming correct?  How to confirm?
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Sorting and reparenting issues
Reply #7 - Nov 14th, 2017 at 10:06pm
Print Post Print Post  
obfusc88 wrote on Nov 14th, 2017 at 9:45pm:
Is this programming correct?  How to confirm?


No it is not correct. Syntax wise, XResultSetClose() takes the pointer to the XResultSet so vOrdersSet and vLinesSet in the case of your code.

Logic wise, NO STOP BAD! You should not be doing this with the same records that you have open in a form. In fact you should never be modifying values with any of the XResultSet commands of a record that you have open on the same form(Reparenting modifies the subrecords and parent record). I can not stress this enough, DO NOT DO THIS!!! If these are the freshly imported subrecords, they are already parented to the newly created parent. Changing a key value will not change that fact.

If this is some other case where the code just happens to be the same, You can check @Error() after the @XResultSetReparent() to check it's success.


-Ray
  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Sorting and reparenting issues
Reply #8 - Nov 15th, 2017 at 1:57am
Print Post Print Post  
Okay, really good info: If these are the freshly imported subrecords, they are already parented to the newly created parent. Changing a key value will not change that fact.

Summary of my understanding now:
1. Original order, linking field of OrderNumber, value is 100.
2. Exported record with subform lines.
3. Went to Add, and imported the exported record 100 with subform lines.  New natural linking is created between New record and subform lines that were imported.
4. Changed the order number of newly added record from 100 to 200.
5. So, new subform records are now linked to the new record OrderNumber 200 even though they were originally linked to 100?

Two records now exist, OrderNumbers 100 and 200, each with their own subform line records. 
On the subform line records, I can identify the Parent Order as 100 or 200 to match the natural linking.

Correct?  So this is why NO Reparenting is needed.
  
Back to top
 
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Sorting and reparenting issues
Reply #9 - Nov 15th, 2017 at 2:48pm
Print Post Print Post  
Correct. The newly created child records are parented to that newly created parent record regardless of what ID number is on the parent or the child record(s) as they are naturally linked.

-Ray
  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Sorting and reparenting issues
Reply #10 - Nov 15th, 2017 at 2:54pm
Print Post Print Post  
Thanks for making that clear. Good to understand.
  
Back to top
 
IP Logged