Repeating Fields

[OffTheDeepEnd.com]

Hello,

Maybe there is a solution to this but I just cannot think of one. What I have is a Purchase Order Database that has 7 repeating fields with 18 repetitions. The Fields are "Qty.", "Mfgr Part #", "our Item #", "Desc", "Min Pur", "Cost Ea." and "ext'd". There are lookups on our item number that populates the description, mfgr part #, min pur, and cost ea. What I need to do is switch over to a line items database so that way the data can be analyzed better. What would be the best way to export the data into the new database?

[Ugo DI LUCA]

Hi,

This would require a loop script, but basically is nothing impossible.

I'd suggest you start by creating a calculation in your current File :

Count(Item#) .

This will give you the number of repetitions really used for each record.

Create a global field g_MainID, that you'd use to temporary store the current Record_ID.

Add a g_countItems, global number, that you'd use to store the result of the Count().

In the Line Item, create a relationship from the LineItems to the Main File using a constant relationship.

And a counter (global, number) that will be used either as a start/end of your loop according to the number of items, as well as the variable to parse each repeating field in a calculation :

SetField[item#,GetRepetition(::Constant:Item#, gcounter)]

You'd need as many steps of this kind as the number of fields you'd like to parse.

So when the ID and g_CountItems are populated in the Main file, you'd be calling a script in the Line Item, which should be something like :

Loop

Create NewRecord

SetField[ForeignID, ::Constant:g_Main_ID]

ExitRecord[] ---This will help triggering lookups from Rel Foreign_ID::Main:ID -

Loop

SetField[item#,GetRepetition(::Constant:Item#, gcounter)]

SetField[Qty,GetRepetition(::Constant:Qty, gcounter)]

...

....Other SetField

...

EndLoop

SetField[g_counter, g_counter+1]

EndLoop If [g_couter>Constant::g_CountItems]

EndLoop

Surely a good decision anyway to get rid of repeating fields in a Purchase Order solution.

Forgot to say. This loop script will be called as an external script in another Loop in the Main File.

ShowAllRecords

GoToRecord[First]

Loop

SetField[g_MainID, ID]

SetField[g_countItems, Count(Item#)]

PerformExternal Script

GoToRecord[Next-End script after last]

Back up your file first.

[ernst]

When importing data into the new file you have the option to split repeating fields into separate records.

Regards,

Ernst.

[Ugo DI LUCA]

Never used this feature, so I always forgot this option. Might be a little more easy then

[OffTheDeepEnd.com]

ok, back to the question again that I posted a couple months ago, as we are finally getting around to do the switch, I tried the import method that was listed above and have no clue how to do that. I am going to try the loop but I think that it causes a problem with the point that every now and then there is random typing in one of the fields that the count might not be on. I have included a picture of the database. Any help would be appreciated.

[bruceR]

Importing and splitting repeats is extremely easy. Just do your field matching and proceed to the next step, when you will see this: