Hello FM Pros

Would appreciate some tips for the following script problem:

 I have a situation here where my scripts in FM 13 Pro Adv takes more than 5 hrs to complete a calculation.

OS: This is Win 7 Pro. with 16 GB RAM (i5 Processor.)


I have Field "A" which is a 7 digit code numbers FIELD, which most often are repeating numbers, from 200 different types.

(And I have upto 80 million records, in this field.) Code examples: 1234567, 2345678, 5678012,...

Fields "B" to "Y" (are global Fields) provides a reference number for each type of the 7 digit codes from Field "A". For

example the code 1234567, as above, the reference is, eg. HH/2015/Jul/132, Code 2345678 the reference is, HH/2015/Sep/314,...etc

Field "C" list the reference numbers from Field "B" in the order of the records from Field "A"

I use the following scripts in Field "C"

If [Field A="1234567"]
Perform Script [Replace Field Contents Field C;Field B]
Else If [Field A="2345678"]
Perform Script [Replace Field Contents Field C:Field B]

Else If (...until 200 lines of the references from Field)

End If

The script runs well. However, it takes more than 5 hrs to complete the entire 80 million records.)

Would appreciate your suggestion to speed run the scripts.

Many thanks in advance.

Lawrencex

 

 

It's hard to tell, but it sounds like there are a couple fundamental changes you can make.

You should move to having another table. Instead of a script with 200 If/Else If options, create a table with 200 records. On field would be the code, another field would be the reference.

Then you build a relationship between the two tables and use an auto-enter calculation to set Field B.

It's not entirely clear if Field A contains a single 7 digit number or a series of them (up to 25?). If a series, you should split those up into separate records as well. Also, are you getting 80 Million new records frequently or is this a one time process? You might be able to save a lot of time by using relationships rather than scripts, but 80 million records is a lot to update.

In short, this isn't a scripting problem as much as it is a data structure problem.

It would be helpful as well to have the actual data rather than samples. It is easier to understand. A sample file would be great.

I agree with David, with only a minor change. All you need to do is:

1. Add a table of Codes, with two fields (Code and Reference), and populate it with 200 records;

2. Define relationship between the two tables, matching on Code.

There is no need to populate another field in your existing table with the corresponding reference; just place the Reference field from the Codes table on the layout of your existing table (make it non-enterable to prevent accidental modification).