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?

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.

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

Regards,

Ernst.

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

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.

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