How do I do this

[jagoldst]

I have a database with 5000 records. It is a flat file with a field called style. For each different style there are between 1-5 records. I want to write a script that will go thru all 5000 records and number the corresponding records with the same style example if style=b234 and there are 5 records with B234 I want B234-1, B234-2, B234-3, B234-4, B234-5.

Thanks for your help

Jeff

[Peter Fenner]

Would B234-1 etc. be replacing your existing style number B234 or would this be in another field?

pete

[jagoldst]

Peter, It would be another field.

Thanks

Jeff

[paulage77]

Start by making a relationship on the styles field. A self join.

Create another field as an index counter field.

Loop through related records

SetField["new_field", style & "-" & index]

SetField["index", index+1]

End Loop

[paulage77]

Sorry for the incomplete algorithm and psuedo-code

[jagoldst]

Paul,

You have lost me. I logically know what to do but am not that versed in scriptmaster

How do you make a relationship on the styles field

How do you loop thru the records

Jeff

[Ugo DI LUCA]

Paul is correct, the above could be set using the selfjoin on styles.

You would need these new fields :

- a global for the index

- a count(selfjoin) that will determine how many index should be added and when to stop the llop script.

- a global for the temporary key fro a relationship g_style::style.

Then your script for each related record would go :

Setfield (g_style, style)

Go to related record

Set field (g_index, 0)

Loop

Sefield (new Id, old Id & "-" & g_index+1)

Set Field (g_index, g_index+1)

Go to field (next)

End Loop if g_index = Count(selfjoin)

End Loop

[jagoldst]

Anyone have any other ideas....

Thanks

Jeff

[paulage77]

First to do the relationship, go to Define Relationships, goto new, and locate the file you are currently looking at, since the goal is actually to do a self join. Select the file and you will be presented with a list on the left and one on the right, find "style" in both the right and left boxes and highlight them. Also give it a name at the top like "Self__style". Then select Okay. You will now have the relationship that you want set up.

This will basically set up a relationship that groups all records with the same "style" name together. Next step is to create a script that will loop through these groupings and create a unique name for each one.

Script will be something like (somebody better at this can help you more ... but ...)

SetField["index",1]

Loop (through all records)

Loop

if (new_field ="")

SetField[new_field, style & "-" & index]

SetField["index", index+1]

Go To Related Record(based on Self__style/end if last)

End Loop

SetField["index",1]

End Loop

Maybe somebody has a better way or can fill in correct syntax for you, but that's the jist of it.

HTH

[paulage77]

Ahh... Thanks Ugo for filling in the correct syntax Go with that algorithm and see my notes for doing the self join, and you should be in business

[jagoldst]

Ugo's solution is working except

Sefield (new Id, old Id & "-" & g_index+1)

Set Field (g_index, g_index+1)

It is returning the style plus the total count.

Any suggestions

Jeff

[Ugo DI LUCA]

OK, back-up your files first and have a try with this one....

You need these fields :

Record_ID

Style

New Style

g_group (global num field)

group (num field)

g_counter (global num)

ConcanateG = Style & g_group

Concanate = Style & group

You need 3 relationships :

R1 = SelfjoinOnStyles ----> Style::Style

R2 = SeljjoinOnGroups --> g_group::group

R3 = SelfjoinConcanate -> ConcanateG::Concanate

and last :

countRel = Count(R1::Record_ID)

Drop the group field on layout as we need it in a replace step

Then the script :

Show All records

Go to record (first)

Set Field (g_group, 1)

Set Field (group, 1)

Replace (group, 1 - no dialog)

Loop

Show All records

If (Is Valid (::R2:record_ID))

Go to Related records (R2) - Show only

Go to Related records (R3) - Show Only

Go to Record (first)

Set Field (g_counter, 0)

Loop

Set Field (StyleNew, Style & "-" & (g_counter+1)

Set Field (g_counter, g_counter+1)

Set Field (group, 0)*

Go to record (next- end after last)

End Loop If (g_counter = Count(R1))

End Loop

Else

Halt script

End If

End Loop

* What we did here is that we created a relationship using a group_ID to determine the found set. Then, when a new Style is created, the group ID is replaced with 0, that doesn't match g_group (1) anymore.

This way, when in the loop, a Go to related record step is called, all records already created are omitted.

Hope this works.

[Ugo DI LUCA]

Now, note that there are other methods than a script to do this.

The first method would consist in a seljfoin on Next Record_ID, using a look-up. But you need to already have a serial and the next serial number created.

Another method, which you should prefer is the use of a ranking calculation based on the selfjoin On Styles, therefore concanation both style and rank in a calc field = Style & "-" & Rank.

This is the second time in a week that I'm linking Ray's file (Cobaltsky), but I'm quite sure this time it will do what you're looking for.

Have a look at his site and download the sampler "GroupRanking" which is a complex range ranking calc.

You may have a look at a thread called "Calculate a record Number", about a year or so on the forum.