assign incrementing values to child records, start at zero with each parent key

[kthibeault1965]

Hello

does anyone know how to run and update script that will assign incrementing numeric values to child records, based on their association with the parent?

eg cars and color choices, update below color_choice_number

if a car has 8 color choices, the color_choice_number fields of the CHILD records would read 0,1,2,3, -> 7 (being the eighth choice)

parent = car_id

child = car_id, color_choice, color_choice_number

thanks !!!

[Stan Mathews]

Re: assign incrementing values to child records, start at zero with each parent key

Along these lines?

Numbering browse lines.

[kthibeault1965]

Re: assign incrementing values to child records, start at zero with each parent key

Along these lines?

Numbering browse lines.

this approach does work - but SLOWLLLLYY ... It took 60 seconds to update 3 rows, on a speedy new machine. the child records are about 5000 in total, but each child grouping is about 2-3, up to about 30 child records. any hints to speed this bad boy up??!

[Tom Cone Jr]

Re: assign incrementing values to child records, start at zero with each parent key

Keven, is this something that would be done only once? If it's apt to be run periodically, is it ok if the id number for a given color changes or do you need to lock the number down? I'm thinking of a custom script that sorts the child table by car_id, and then by color_choice. The script would then fetch through all the records using a loop to begin the number sequence again when the car_id field value changes. This could be used to populate the color_number fields. But you wouldn't want to run it twice if the color choices for a car might have changed. Still, if a script like this would be useful let me know.

[Mike Wilson]

Re: assign incrementing values to child records, start at zero with each parent key

K,
I don't know about an update. If you are just looking for the next number in sequence for that particular car_id when entering records, then try this on a New Record button, or a form or field event of your choice.

[COLOR="Blue"]'variable for the next number. if only 0-8, this could be Character[/COLOR]
   dim vnxtno as N     
[COLOR="blue"]Variable for car_id.  This should be a character field since not likely 
' involved with math functions[/COLOR]
   dim vcarid as C 
[COLOR="Blue"]' acquires parent car_id value[/COLOR]
   vcarid = parentform:car_id.value  

[COLOR="blue"]'establishes the filter for the child tables[/COLOR]
   vfilter = "car_id = "+quote(vcarid)	

[COLOR="blue"]' probes the child table to check if first record, if yes, then next =0, 
' if not, increment from the max[/COLOR]
   if a5_get_records_in_query("childtablename",vfilter) = 0 then
      then vnxtno = 0
   else
      vnxtno = increment_value(tablemax("childtablename",vfilter,"color_choice_number"))
   end if
[COLOR="blue"]' use the value in vnxtno for the color number for the next record[/COLOR]
parentform:color_choice_number.value = vnxtno

LATER:
Actually, all you'd need would be this:

dim vnxtno as N
	
vnxtno = a5_get_records_in_query("childtablename","car_id = "+quote(vcarid))
parentform:color_choice_number.value = vnxtno

[Tom Cone Jr]

Re: assign incrementing values to child records, start at zero with each parent key

Good morning, Mike.

We need Keven to clarify his situation. Is he talking about assigning the next color_choice_number to each child table record as it gets entered, or is he trying to assign grouped sequential numbers to all the child table records in the table ?

[Mike Wilson]

Re: assign incrementing values to child records, start at zero with each parent key

Good morning, Tom,
Yes, I see, you are absolutely right. He does use the word update, twice, doesn't he. The water is boiling now, so the first cup of coffee should be taking hold soon...

[kthibeault1965]

Re: assign incrementing values to child records, start at zero with each parent key

Yes... I actually need to have the row count, run as a global update to a set, or to two tables.

I dont need it just to display, if there are 20 child records, I need to sequence the values 1,2,3->20 for each of the child records

As i mentioned, the script works, while viewing a set form, but is so incrediblly slow, it is faster to export the data to excel, and MANUALLY cut an past down the file.

so obviously any help would be greatly appreciated

[Tom Cone Jr]

Re: assign incrementing values to child records, start at zero with each parent key

Is he talking about assigning the next color_choice_number to each child table record as it gets entered, or is he trying to assign grouped sequential numbers to all the child table records in the table ?

K, I can't tell which question you're answering, and don't understand your answer. Would like to help, but need you to try harder to describe exactly what you need, and the context in which the process would run.

-- tom

[kthibeault1965]

Re: assign incrementing values to child records, start at zero with each parent key

Assign the grouped sequential numbers globally in a single pass via an update script.

NOT during data entry mode

thanks again!

[JohnZaleski]

Re: assign incrementing values to child records, start at zero with each parent key

Kevin
It woud only involve a simple default field rule to assign the sequence number
during data entry which would negate the slowness of your update program.
Could you explain why this would be unsatisfactory.The end result would be the same wouldn't it?
John

[Tom Cone Jr]

Re: assign incrementing values to child records, start at zero with each parent key

K, thanks. Unless you care to post sample tables for us to use I plan to use the invoice header and item tables in AlphaSports. Will change structure of the items table to include a sequence number field, and then will write a script that steps through the items table numbering items for each header record consecutively. This should be easily adaptable to your situation I should think. -- tom

[Tom Cone Jr]

Re: assign incrementing values to child records, start at zero with each parent key

K,

Here's the example for you. Unzip and extract to an empty folder.

I've defined an update operation to zero out all the seq field values.

Then I've written a script to renumber them sequentially. You'll find the script on the code page of the control panel.

-- tom

[kthibeault1965]

Re: assign incrementing values to child records, start at zero with each parent key

THIS EXAMPLE WORKS LIKE A CHARM! Thanks to Tom Cone Jr - and his clever coding, this will make the application run like a champ

THANK YOU Tom !!

[dik_coleman]

Re: assign incrementing values to child records, start at zero with each parent key

Interesting!!
Why does it have to be done 'after the fact'?? Why not do it as the record is made??
Use the parent record incrementing id and add a sequence number to it. Record id's do not have to be automatic, incremented values. Record numbers can be used for that kind of query. Doing that would make default browses easier to read and dissect.

Deserves a good look!!
D