Lots of countif and need a practical way

[ihsan07]

Hi people. My script working slowly and any idea to speed things up appreciated.
I import records about 60000 long to a field.
And i have 60 more global fields with named as values from imported field
i wrote a looping script which gets the name of active field, match it from imported field and counts the number of matching values in the imported field.

Does that for 60 times and it get really slow. It is a commomly used script and i need a more practical way. Here is code:

 

Go to Layout [“dış veri” (Dış veriler)]

Set Error Capture [On]

Allow User Abort[Off]

Delete All Records [No dialog]

Import Records [Target:“Dış veriler”; Method:Add; Character Set:“Unicode”; Field Mapping:Source field 1 import toDış veriler:: KONUM Source field 2 import toDış veriler::IST_SUC_KODU Source field 3 import toDış veriler::FAIL_DURUMU] [No dialog]

View As [View as Table]

Go to Field [Dış veriler::İn_tic_b]

Go to Record/Request/Page [First]

Loop

Set Field[ValueCount ( FilterValues ( List ( Dış veriler 2::CASE ) ; GetFieldName(Evaluate(Get(ActiveFieldName)))))]

Go to Next Field

Exit Loop If [if ( GetFieldName(Evaluate(Get(ActiveFieldName))) = "Dış veriler::Global sabit" ;1 ; 0 )]

End Loop

Go to Layout [original layout]

 

 

[Fitch]

Interesting problem. One thing I see is that you're calling the same List function over and over again. List is generally fast, but for 60k values... it might help a little to put the list into a variable, above the Loop, and then do your FilterValues on the variable.

[Ocean West]

I am not sure but i thought that viewing as TABLE or LIST is slower in some cases where as FORM view performs faster?

 

I am also not too clear on your schema but wonder if this technique can be of help?

 

http://www.modularfilemaker.org/2013/03/hyperlist/

[Fitch]

Yes, form view is worth a try, I thought about that too. Might need to update the layout to make sure all the fields are there, and the tab order is OK. But it sounds like he's just sitting on one record, moving from field to field, so I wouldn't think it would make a huge difference. Only one way to find out.

 

Freeze window would also be worth a try.

 

Another thing that might speed it a little is to not move from field to field. Instead, you could get a list of fields on the layout into a variable using the FieldNames function, and then step through that field list in your loop, using Set Field by Name.

Oh yeah, that hyperlist stuff is surprising, you'll definitely want to think about that.

 

And then there's ExecuteSQL -- it's not necessarily going to be faster, but I'd still test it.

[ihsan07]

Found the problem. global field is a calculation field and script doesnt select this field. This means infinite loop. Is there a better way to loop through spesific fields?

[ihsan07]

By the way using list function as a variable dropped script running time from 10 min to two seconds. Thanks for your ideas. They really helped.

[Manjit Behera]

1. To speed up the process first up all make sure that the layout you are using must not contain more fields than you need and no any unstored calculation field over there.

2. Need to set the layout to be view in only form view. To restrict other view you need to goto Layout setting and disable Table and List views option.

 

Layout Setup -> View -> Form View [only]

 

3. If you are using this layout in Table/List view what you can do is just duplicate the layout and did all the above settings and do all operations on that layout and then return to your original layout if you want.

 

4. Instead of using global fields and list of values into a field you can use virtual list or global variables to hold the import data.

 

Thanks,