Look up value from list stored in a different database

7 posts / 0 new
Log in or register to post comments
Last post
Wed, 11/04/2020 - 11:19
#1
VITOPIEPOLI
Offline
Joined: 10/29/2019 - 11:28
Look up value from list stored in a different database

Hi is there a way when using @isin or @isini to point to a list values stored in a different database?
I have a list of values created using the following code attached.
I need to sift through the GL transactions and pick up the transaction related to the names in the list.

Thank you for your help.

Kind regards,

Files: 
Binary Data CreateList.iss
Top
Wed, 11/04/2020 - 15:27
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
You can't point to a list directly and just use it. The easiest way is to read the list into an array, so the information could be in another IDEA database or a text file or from a dialog where users input it in. This is stored in an array and then the array is used to create an equation using the @isin or the @isini function. You have to watch out for limitations as there is a 10,000 character limitation for the equation editor so if you have a large list it might not fit into the equation editor.
Top
Thu, 11/05/2020 - 05:36
(Reply to #2)
VITOPIEPOLI
Offline
Joined: 10/29/2019 - 11:28

Thank you for your reply Brian,
If I understand, in this specific case, I should read the names from the database created (I would like to keep it for review) and point the @isin or @isin function to the array variable.
Now, could you be so kind to direct me towards the functions I should look at?
Is the Database.RecordSet example on any use?

Top
Thu, 11/05/2020 - 06:57
(Reply to #3)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
This is a bit more complicated subject. Let me see if I can find some time to create a video on this as I think it would then be easier to follow then trying to write it out.
Top
Thu, 11/05/2020 - 14:30
scotchy33
Offline
Joined: 09/05/2012 - 15:51

Hi VITOPIEPOLI,

You could try using the attached script, it may do what you want. I have attached some screenshots on how to use it.
1) The first screenshot is of a general ledger description field. In the description field there is a Province Id located somewhere in the field. We want to identify the Province Id and also add in the Province Code
2) Screenshot two shows the parameters to choose in this particular case.
3) Screenshot three shows the resulting file that is created

In your situation I believe you want to search for people/management names, so your master file would be the list of names to search for.

Scott

Images: 
Files: 
Binary Data ISINI Join v1.iss
Top
Mon, 11/16/2020 - 07:14
(Reply to #5)
VITOPIEPOLI
Offline
Joined: 10/29/2019 - 11:28

Thank you very much for your reply.
It is exactly what I need. However, being my code already hardwired with a fixed sets of fields and databases I am trying to understand what does what and the bits that I need within my own code.

Again, thank you.

Vito

Top
Wed, 11/18/2020 - 06:28
VITOPIEPOLI
Offline
Joined: 10/29/2019 - 11:28

Update:
I managed to get the script working but it take around 30 minutes to parse 140K records with 4 words to look up. A simple @isini function take around 1 second to do the same check with a specific word.
I assume it is due to the opening and closing of the databases at each iteration.

Before starting to look into, are you aware of ways for Python to access IDEA tables and work with it? I am asking it because I have written down the Python code to do the job but don't know if it 100% compatible with Idea working environment.

Many thanks

Vito

Top