Client.OpenDatabase

hwes

Forums

Hello,
I'm new here and looking for an easy wildcard (hopefully the right term in English) in the database-name.
At the ? below It's not working with % or * or $ of "?" and I want to make one script for all years.
 
Part of the code:
------------------------------------------------------------------------------------
Dim pad As String
Dim reg As String  
Dim db As Object
Dim dbName As String
pad         = Client.WorkingDirectory   
Set db = Client.OpenDatabase ("SRT" & ? & "_financial_table.IMD")
and so on------------------------------------------------------------------------------------
There are 3 posible databases, each in a different folder:
* \Finance\SRT\2011\ SRT2011_financial_table.imd
* \Finance\SRT\2012\ SRT2012_financial_table.imd
* \Finance\SRT\2013\ SRT2013_financial_table.imd
Byhteway, I use IDEA 8.
Sorry for my English, but it is hard to translate into English jargon.

MagiBzh Mon, 07/13/2015 - 06:26

Hi,
First, I would like to thank you Brian, this site has very good and useful content.
I am looking for the same script as described below, but the variable will be a text. For example, most of my files have :"AP_INVOICE_DISTRIBUTIONS_ALL" in their file name. But usually there are two characters in addition, sometimes at the begining or at the end.
Also, some of the filenames are in capital letters, and others aren't. Could you help me to change the script above?
Kindest Regards

Brian Element Mon, 07/13/2015 - 07:25

Hello MagiBzh and welcome to the site, I am glad you are finding it useful.

When you have some mixed case you can use LCase to make it all lowercase or UCase to make it all upper case and then you can do the comparison.

So could you give me some examples of the file names, how do you know if they start or end with two additional characters?  Also what do you want to do with them, do you want to extract the two additional characters and based on that do something with the file or is this just to read the file. 

Once I have a bit more info I wil put something together for you.

Brian

MagiBzh Mon, 07/13/2015 - 10:00

 I imported 5 databases with all their fields, but actually, only several fields interest me in these files.
But I have to import all of the fields just in case. Then, I have to do several extractions.
I have a script that can make all the extractions, but I need to change manually the name of the database in the script. This script was created from the history.  
Some of my colleagues need my script, so I need to make it more users friendly.
I made one that pop up a dialog box so you can choose the database concerned for each extraction. But I would like to not have to use the dialog box that shows all the databases in the file explorer.
 
I have 5 extractions to make; each one has specific fields I need. The names of the files are often the same:
            - ap_invoices_all
            - ap_checks 
          - ap_invoice_distributions 
           - vendors
           - vendors_sites
This is the words that are always in the file names, but sometimes there is the code of the company that can be at the beginning of the filename or at the end.
I didn’t manage to define correctly the variables :(
I also would like that the name of the new database created has the code of the company. Do you think it can be possible?
 
I did not manage to upload the script, I have IDEA 9.2.0.630 (X86 Unicode), here is an extract:
 Sub Main         Call DirectExtraction1() 'From Ap Invoice Payments  Call DirectExtraction2() 'From Ap invoice distribution
 End Sub
 
' Data: Direct Extraction Function DirectExtraction1  Set db = Client.OpenDatabase("ap_invoice_payments_all_ZY.IDM")  Set task = db.Extraction  task.AddFieldToInc "ACCOUNTING_EVENT_ID"  task.AddFieldToInc "ACCOUNTING_DATE"  task.AddFieldToInc "PAID_AMOUNT"  task.AddFieldToInc "CHECK_ID"  task.AddFieldToInc "INVOICE_ID"  dbName = "AP_invoice_payments_ZY.IDM"  task.AddExtraction dbName, "", ""  task.CreateVirtualDatabase = False  task.PerformTask 1, db.Count  Set task = Nothing  Set db = Nothing  Client.OpenDatabase (dbName) End Function
 
' Data: Direct Extraction Function DirectExtraction2  Set db = Client.OpenDatabase("PP_invoice_distributions_all_ZY.IDM")  Set task = db.Extraction  task.AddFieldToInc "DIST_CODE_COMBINATION_ID"  task.AddFieldToInc "INVOICE_ID"  task.AddFieldToInc "LINE_TYPE_LOOKUP_CODE"  task.AddFieldToInc "PERIOD_NAME"  task.AddFieldToInc "AMOUNT"  task.AddFieldToInc "DESCRIPTION"  task.AddFieldToInc "PO_DISTRIBUTION_ID"  task.AddFieldToInc "ORG_ID"  dbName = "PP_Invoice_distributions_ZY.IDM"  task.AddExtraction dbName, "", ""  task.CreateVirtualDatabase = False  task.PerformTask 1, db.Count  Set task = Nothing  Set db = Nothing  Client.OpenDatabase (dbName) End Function
 
And final question, on this post: http://ideascripting.com/Check-if-field-name-exists
 I didn’t manage to make it work… The script runs, but no error message, it just opens the database. I would like to add this script in my script that makes the extractions, in case the field has another name (For example, the field “amount” can be called “check_amount” in another database), or cannot be found, and then the extractions would stop.
 
I hope I have give you enough details, but if you need more precisions, feel free to ask :)
 

Brian Element Mon, 07/13/2015 - 10:25

Thanks for the example.  So in this case you have two files ap_invoice_payments_all_ZY.IDM and PP_invoice_distributions_all_ZY.IDM, so the first file would relate to the ap_invoices_all but what would the second file relate to, ap_invoice_distributions?  I am just trying to understand the filename structure and how it can be used, is it always standard file names or can it be different each time?  Also would you want the script to read the idm files from the directory and based on the filename structore do some type of extraction?  This can be done if we can figure out what are the common elements in each filename so the script knows what to look for.

From what I understand the field name exists might not be what you are looking for as what it does is look and see if a fieldname exists and if so it returns a different fieldname.  It sounds like you are looking for the field name of a specific field, do you know if this field is always in the same location of the file (such as always being field 5?), if so I can write a function that will obtain the fieldname that you can use.

Thanks for flagging that you couldn't upload, I just realized I never set-up the site for the unicode file formats, only for the ascii version.  I will make the updates tonight.

MagiBzh Tue, 07/14/2015 - 06:51

 
Hi Brian,
 
Many thanks for replying to me.
 
 So in this case you have two files ap_invoice_payments_all_ZY.IDM and PP_invoice_distributions_all_ZY.IDM, so the first file would relate to the ap_invoices_all but what would the second file relate to, ap_invoice_distributions? 
 
Yep, actually, each of this databases (invoice payments, invoice distributions) will be join after. But only some of their fields interest me. I have the list which is in the extraction script e.g “task.AddFieldToInc "INVOICE_ID"”.  
 
 
I am just trying to understand the filename structure and how it can be used, is it always standard file names or can it be different each time? 
 
Also would you want the script to read the idm files from the directory and based on the filename structure do some type of extraction?  This can be done if we can figure out what are the common elements in each filename so the script knows what to look for.
 
Yes, this is what I would like to do :). I will always have invoice_distribution or invoice_payments in the name of the database/file. But, depending on the subsidiary code, the name can slightly change: “AP_INVOICES_ALL_SW” or “LL_AP_CHECKS”. For each subsidiary I have the same type of  file/database, just a few fields might change but it is very rare.
 
The common elements are in the name of the file/Database:
 
                AP_INVOICES_ALL
 
                AP_CHECKS
 
                AP_INVOICE_DISTRIBUTIONS
 
                VENDORS
 
                VENDORS_SITES
 
 
From what I understand the field name exists might not be what you are looking for as what it does is look and see if a fieldname exists and if so it returns a different fieldname.  It sounds like you are looking for the field name of a specific field, do you know if this field is always in the same location of the file (such as always being field 5?), if so I can write a function that will obtain the fieldname that you can use.
 
 Yes you are righy, I checked:
 
” field 2 for “accounting_date” that can be also called payment_date.
 
* field 4 for “amount” than can be called in “paid amount”.
 
But also, I have fields name that have the same name, only the numbers change e.g “segment11”; “segment 23”; “segment 24” etc. This series of  field names start always from field 48.
 
For the moment, I still change the script if it doesn’t find the segment with his good number, but do you think I can put in my fields’ extraction a code that will enable the extraction of all the fields whose name starts with segment ?
 

Brian Element Tue, 07/14/2015 - 17:03

Here is some code that will read the project folder and place all the imd (you will have to replace the file extension as you are using the unicode version I think) and it then looks for those names and you can place the code on what you want done with those files in there.

Option Explicit
Dim sFiles() As String

Sub Main
                Call getFiles()
                Call performExtract()
End Sub

Function performExtract()
                Dim i As Integer
                
                For i = 0 To UBound(sFiles)
                                If iisin("AP_INVOICES_ALL", sFiles(i)) Then

                                ElseIf iisin("AP_CHECKS", sFiles(i)) Then

                                ElseIf iisin("AP_INVOICE_DISTRIBUTIONS", sFiles(i)) Then
                                
                                ElseIf iisin("VENDORS", sFiles(i)) Then

                                ElseIf iisin("AP_INVOICE_DISTRIBUTIONS", sFiles(i)) Then
                                
                                End If

                Next i
End Function

Function getFiles()
                Dim objFSO As Object
                Dim objFolder As Object
                Dim colFiles As Object
                Dim objFile As Object
                Dim obsStartFolder As String
                Dim sFilename As String
                Dim i As Integer
                
                ReDim sFiles(0)
                Set objFSO = CreateObject("Scripting.FileSystemObject")
                                obsStartFolder = client.WorkingDirectory
                                Set objFolder = objFSO.GetFolder(obsStartFolder)
                                                Set colFiles = objFolder.Files
                                                                For Each objFile In colFiles
                                                                                sFilename = UCase(objFile.Name)
                                                                                If Mid(sFilename, Len(sFilename) - 2, 3)  = "IMD" Then
                                                                                                If i = 0 Then 
                                                                                                                sFiles(0)= sFilename
                                                                                                                i = 1
                                                                                                Else
                                                                                                                ReDim preserve sFiles(UBound(sFiles) + 1)
                                                                                                                sFiles(UBound(sFiles)) = sFilename
                                                                                                End If
                                                                                End If
                                                                Next
                                                Set colFiles = Nothing
                                Set objFolder = Nothing
                Set objFSO = Nothing
End Function

 

Brian Element Tue, 07/14/2015 - 17:16
Here is an example of reading the field names into an array that you can then use in your script. 
Option Explicit
Dim sFields() As String

Sub Main
	Dim i As Integer
	Call getFieldNames("General Ledger-GL.IMD")
	For i = 0 To UBound(sFields)
		MsgBox sFields(i)
	Next i
End Sub

Function getFieldNames(sFilename As String)
	Dim db As database
	Dim table As table
	Dim field As field
	Dim i As Integer
	Dim count As Integer
	
	
	Set db = client.OpenDatabase(sFilename)
		Set table = db.tabledef
			count = table.count
			ReDim sFields(count - 1)
			For i = 1 To count
				Set field = table.GetFieldAt(i)
				sFields(i - 1) = field.name
			Next i
		Set table = Nothing
	Set db = Nothing
End Function