Create a new database including a concatenate field from several recors in other table

lliberal1984

Forums

Dear all,
I have a test that I would like to do in IDEA, yet I bought the book Mastering IDEAScript and searched in the Help motor of idea and I have no clue how to execute this script. I tought someone might help me here. My requirement is described in the picture below.
 
The objective is to search on an existent table if a record matches a condition (If(@left(Account) =="00006" .AND. Debit/Credit =="C"). This table consists on a manual journal entries where we have for each transaction different movements in Credit and Debit. My objective is, if I have a movement that matches this condition I want to create a record in a new database (should only be created if there are movements fulfilling this condition) which contains the Account number, the debit credit move and a field which is a result of the concatenation of the accounts for all the debit movements (records) related to this transaction.
Please refer to the picture below to see a graphical example.
Did someone performed something similar that can share with me?
Thank you in advance.
Best regards,
Luís.
 
 

Brian Element Tue, 09/16/2014 - 07:20

Hi Luis, and welcome to the site. 

So I think you have to do this in two parts, the first part would be a simple extraction using your equation, this would give you all the Credit accounts that have an account number starting with "00006".  The second part is more difficult.  You could use a join to pull off all the debits but that won't get what you want.  I believe you will have to do this with a script, unfortunately the book Mastering IDEAScript won't give you what you want as it is good at showing you the different pieces but falls short in putting everything together.  I could put something together for you as what you want is not that difficult. 

Also maybe another super user Steve might have an idea on how to do the second part without using a script but if there is one it is not coming to me right now.

Brian

lliberal1984 Tue, 09/16/2014 - 07:51

Hello Brian,
I was trying to build a script to execute this. But I am new to IDEAScript and I do not know the syntax for the methods and properties. It would be great if you would be able to give me some hints regarding some IDEAScript code that I can use to perform this second part.
Thank you a lot for the response.
Best regards,
Luís.

Brian Element Tue, 09/16/2014 - 08:34

Hi Luis, I will put something together for you.  The first part is fairly easy to do in ideascript, the second part is more complex and you wouldn't find this in a book.

Brian Element Wed, 09/17/2014 - 18:46

Hi Luis,

Try this out, it took me a bit of time to figure out the logic to get the account name properly into the debit accounts but I think I did it.  It has only had limited testing and I didn't put in any error checking in the dialog.

First you have to select the file and then you have to select the transaction number field, account number field, account name field and the debit/credit field.  For the debit accounts field I have put it at 150 but you can easily change it in the script as I have made it a constant at the top of the script.  The maximum size I believe you can have is 1,024 characters.  

So let me know if you find any problems with it.

Brian

lliberal1984 Mon, 09/22/2014 - 03:24

Dear Brian,
Thank you a lot for this. It really helped.
Best regards,
Luís.