Pass the filename from the 1rst function to the 2nd

MagiBzh

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 :)
 

Brian Element Tue, 07/28/2015 - 08:17

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

MagiBzh Tue, 07/28/2015 - 08:45

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.

Brian Element Tue, 07/28/2015 - 09:16

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

Brian Element Tue, 07/28/2015 - 09:16 

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

 

Brian Element Tue, 07/28/2015 - 09:16 

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

 

MagiBzh Tue, 07/28/2015 - 10:08

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.

Brian Element Tue, 07/28/2015 - 10:51

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