If statement to apply a field equation

randywb

Forums

I wrote a function that iterates through a database and returns true if a string is found and false if it is not found.
 I inserted the string into code as indicated below to apply an eqn to a field.
 
If StringExists("PayElements-SpecialPay","ELEMENT",rec.GetCharValue("ELEMENT_NAME")) field.Equation = "XXXXXX"Elsefield.Equation = "YYYYYY"End If
When it ran it did not itterate throug the fields it ean only once. I added a loop to iterate through the code.  Lets say I have a 100 record database.  It read the first record and looped 100 times. Then read the 2nd and looped 100 times.  My DB was much larger and I had to kill the process.  
My question is how do I itterate through a database to apply a field equation.  I was doing it in the equation editor but there are a lot of items in the list and it made a very ugly equation.  A table is a much simpler and effecient way of doing it.
 
Thanks,
Randy

Brian Element Mon, 10/17/2022 - 09:33

Hi Randy,

My recommendation would be to try creating a custom function that way you don't have to worry about itterating through all the rows IDEA will do it for you.  Just create your IDEAScript that you want to work in each particular row.

If you have never created a Custom Function here is a video that will walk you through it.

https://www.youtube.com/watch?v=iCOrtzMgHdU

randywb Tue, 10/18/2022 - 12:48

I used the code that I had working in my Idea script and I received an error "Object variable or With block variable not set"  As far as I can tell it is set.  It has a problem with the set db statement. I copied the code exactly and it works when ran as normal Idea code.  Any Ideas.
Thanks,
Randy

Brian Element Tue, 10/18/2022 - 18:48

Hi Randy, is this for a custom function?  If so you don't need the db or tableDef info.  Remember a Custom Function is being run with the Equation Editor so it knows which database it is working on.  What you need to do is send the necessary information to the Custom Function so it does the calculation and returns the result that then can be placed in a virtual field or used as a filter to filter out different records.  You should not need to access databases, fields or records in a Custom Function.

randywb Wed, 10/19/2022 - 08:48

I am filtering on several lists.  They are rather long and cumbersome.  I was looking for a way to look them up and filter where the list is easy to maintain in a seperate database.  They do not exist in the current database I am opening.  What I am asking is it possible to open a 2nd database an iterate through it.
I hope this clears it up a little better.
Thanks,
 
Randy

Brian Element Fri, 10/21/2022 - 08:03

Hi Randy, you can kind of do what you want through a series of joins.  

First you would take your MasterDB and join it with your ListDB1, the match type is "All records in primary file"

You would get the following result.  So where there is a blank that is a no and where there is something that is a yes.

You would then take the file you just created and join it to the ListDB2 in the same fashion, using the "All records in primary file"

This gives you the following result:

This is not exactly what you are looking for but if you wanted a yes/no then you could add on two virtual fields with a @if statement, if the fields is blank it is a No, otherwise it is a Yes.

Hopefully this helps you out.