We have created an Access database that collects transactions from an outside vendor. We would like to import these transactions into the 08.010.00. How can I manipulate the data for import?
Any help would be appreciated.
Do you plan to use Solomon's TI screen to handle the import? What would you need to modify in the Access data prior to importing it into Solomon?
We plan on using the TI screen. We plan on adding the account and project codes from Solomon, that's it.
I use the Transaction Import. I use Generate Control Macro to generate a macro that shows the fields on the screen and their possible values. I generate an Intelligent macro to show me the levels on the screen (which could be batch, document, detail or a separate tab) and then I generate a Simple macro to give me the headings for the columns in Excel - which is where I build my import.
The trick are the different levels and know how to set up your import spreadsheet. If you are making changes to Level 2 in a screen (your Intellegent macro will give more detail on the levels) the you would need replace the level name given to you in the Simple macro with the following: level2,change. You can then fill in just the data you need to import under the other headings for that level. Repeat for every level. In the end, your excel spreadsheet might look something like this after you save it to a .CSV file and open in Notepad (CSV is needed for import):
The commas represent columns you did not fill in with data. Transaction Import will not make changes to or import any value into those fields.
Please note - you generally do not need to include the quatation marks around the 'level0,change' text but make sure they are present when you save as a .CSV file. The magically appear when you.
I hope this is helpful and what you were looking for. This is years of hit and miss with Transaction Import knowledge. I don't know eveything yet but the Error log is a good guide to figuring out what the issue is if the import fails.
While your question has pretty much been answered (create a control macro, export from access to a properly formatted csv) there is another way that plays by the SL rules (not writing directly to the tables) which you might find useful. You could make a button on an access form and using the SL object model fill in the screen. This is for a Journal Entry, but it should be enough to get started.
If JournalEntry Is Nothing Then
Set JournalEntry = MyToolbar.StartApplication("0101000.EXE")
JournalEntry.Visible = True
'get the levels
Set ctcompany = JournalEntry.Controls("ctcompany") 'header
Set cbatnbrH = JournalEntry.Controls("cbatnbrH") 'details
strDataEntity = cbatnbrH.Properties("Level")
JournalEntry.Controls("cautorev").Value = 0
JournalEntry.Controls("cperpostH").Value = perpost
JournalEntry.Controls("cledgerid").Value = "ALLOCATION"
strDataEntity = ctcompany.Properties("Level")
' JournalEntry.New strDataEntity
For i = 3 To 50
dbcr = xlSheet.Columns("N").Rows(i).Value
dbcr = Trim$(dbcr)
If dbcr = "db" Or dbcr = "cr" Then
acct = xlSheet.Columns("K").Rows(i).Value
acct = Trim$(acct)
subacct = xlSheet.Columns("L").Rows(i).Value
subacct = Trim$(subacct)
amount = xlSheet.Columns("M").Rows(i).Value
lngAmount = amount * 100
amount = lngAmount / 100
JournalEntry.Controls("ctrandate").Value = VBA.Format$(transdate, "MM/DD/YYYY")
JournalEntry.Controls("cacct").Value = acct
JournalEntry.Controls("csub").Value = subacct
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13