Pass the filename from the 1rst function to the 2nd
Forums
Hi,
I have 5 joins to make, I put a picture to show what I mean. I would like to use the client.uniqueFileName for the name of each database after each join.
I manage to use the client.uniquefileName for the two first join by using the method "calling a sub-routine" but I can not add the 3rd, 4th and 5th join.
I do not manage to pass the filename from the 2nd function to the 3rd function.
I can not upload my scripts, they are in .ism :(
Thanks in advance for the suggestions :)
Hi Brian,
Hi Brian,
thanks for adding the ism type.
I put the script I was talking about ("Join Test-Phase1")
I might need to do this on several other databases which are in the same project, that's why I try to create this script. the 1rst script I made ("2.Phase 1 - Automatic join from vendors to checks all") join gives one name per database, and if I want to use the script on another database I might have a conflict of names.
Hi MagiBzh,
Hi MagiBzh,
Thanks for the script, see my next post on a possible solution. You will have to validate it, I made the changes in notepad as your file is unicode and my IDEA is an ASCII version (hopefully they will bring the two together in the near future).
So I added some code to make sure the filename as unique and as you will see I passed each filename to the next function. Let me know if you have any problems, like I said I updated this in notepad so I could have easily forgotten something or made a typo.
Brian
Sub Main
Sub Main
dim sFilename as string
sFilename = "Invoices All.IDM"
sFilename = JoinDatabase(sFilename) 'Join with vendor
sFilename = JoinDatabase2(sFilename) 'Join with Vendor sites
sFilename = JoinDatabase3(sFilename) 'Join with payment schedules
sFilename = JoinDatabase4(sFilename) 'Join with invoice payments
Call JoinDatabase5(sFilename) 'Join with ap checks all
End Sub
' File: Join Databases
Function JoinDatabase(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Vendors.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "VENDOR_ID", "VENDOR_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with vendor")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase = dbName
End Function
' File: Join Databases
Function JoinDatabase2(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Vendors Sites.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "VENDOR_SITE_ID", "VENDOR_SITE_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with Vendor sites")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase2 = dbName
End Function
' File: Join Databases
Function JoinDatabase3(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Payment Schedules All.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "INVOICE_ID", "INVOICE_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with payment schedules")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase3 = dbName
End Function
' File: Join Databases
Function JoinDatabase4(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Invoice payments.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "INVOICE_ID", "INVOICE_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with invoice payments")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase4 = dbName
End Function
' File: Join Databases
Function JoinDatabase5(sTempFilename as string)
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Checks ALL.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "CHECK_ID", "CHECK_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Final Invoice File 2014-2015")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
End Function
Sub Main
Sub Main
dim sFilename as string
sFilename = "Invoices All.IDM"
sFilename = JoinDatabase(sFilename) 'Join with vendor
sFilename = JoinDatabase2(sFilename) 'Join with Vendor sites
sFilename = JoinDatabase3(sFilename) 'Join with payment schedules
sFilename = JoinDatabase4(sFilename) 'Join with invoice payments
Call JoinDatabase5(sFilename) 'Join with ap checks all
End Sub
' File: Join Databases
Function JoinDatabase(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Vendors.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "VENDOR_ID", "VENDOR_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with vendor")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase = dbName
End Function
' File: Join Databases
Function JoinDatabase2(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Vendors Sites.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "VENDOR_SITE_ID", "VENDOR_SITE_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with Vendor sites")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase2 = dbName
End Function
' File: Join Databases
Function JoinDatabase3(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Payment Schedules All.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "INVOICE_ID", "INVOICE_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with payment schedules")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase3 = dbName
End Function
' File: Join Databases
Function JoinDatabase4(sTempFilename as string) as string
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Invoice payments.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "INVOICE_ID", "INVOICE_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Join with invoice payments")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
JoinDatabase4 = dbName
End Function
' File: Join Databases
Function JoinDatabase5(sTempFilename as string)
Set db = Client.OpenDatabase(sTempFilename)
Set task = db.JoinDatabase
task.FileToJoin "Checks ALL.IDM"
task.IncludeAllPFields
task.IncludeAllSFields
task.AddMatchKey "CHECK_ID", "CHECK_ID", "A"
task.CreateVirtualDatabase = False
dbName = client.UniqueFilename("Final Invoice File 2014-2015")
task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
Client.RefreshFileExplorer
End Function
hehehe it works perfectly,
hehehe it works perfectly, many thanks Brian !
Can you explain to me the impact of the "Temp" in the sFilename ? :)
I am a complete newbie in IdeaScript, I discover the software 6 months ago^^
If you have any reading recommendations, feel free to share, I am eager to learn.
I am glad to hear it works,
I am glad to hear it works, as I was doing it in notepad so I wasn't sure if I wouldn't forget something!.
There is no impact using Temp, I just have a tendancy to do it when I am using local variables that are being transfered to a function. So variables can either be local or global, global variables can be viewed by the entire script where as local can only be seen within the function or sub. So I was taking a variable created in the main sub and sending it to the different functions, so I had to define a new variable in each of those function to hold the filename and I just used sTempFilename to hold it.
Well welcome to IDEAScripting, glad you are enjoying it. There is only one book out there that is IDEAScript specific, you can find a link to it on my front page, I think they have an electronic version of it also. Either then that you can look at the language browser from within the IDEAScript editor as it has lots of example code, look through what I have done and ask questions and I will try and help out.
Good luck.
Brian
Hi MagiBzh,
Hi MagiBzh,
Sorry about that, you should be able to upload an ism file now. There are several ways to do what you want, it is probably easier to give you advice once I see how your script is set-up.
Also have you tried doing the same join using the Visual Connector, it might give you the results you are looking for in one pass instead of 5.
Brian