How do you do a data lookup using the Object Model? Will be used in data import

(0) Share

Report

Posted on by THansen

Hello,

(I'm an SL newbie- both from a user & developer point of view. I've been tasked with writing a data import application).

What's the "SL object model way" of checking if data already exists? I don't see an obvious API method (searched the documentation for "find", "query", "lookup", etc) but assume there's some idiomatic/best practice way of doing it. Other than traversing the existing data set.

I'm also assuming, based on comments I've seen elsewhere, that the object model is the way to go for import, if you don't have expert level knowledge of the database structure. Eventually the import will be an unattended scheduled job, possibly running as a windows service. Any potential issues to be aware of around that, I'd love to hear about it.

Thanks!

*This post is locked for comments

All responses (7)

Answers (0)

THansen 95 on at

Like (0)

Report

RE: How do you do a data lookup using the Object Model? Will be used in data import

(And to be clear, I am aware of some of the issues with using the API from C#. The application works from UI and command line. Just not as a scheduled task)
THansen 95 on at

Like (0)

Report

RE: How do you do a data lookup using the Object Model? Will be used in data import

It appears that there are two interfaces referred to as the "Object Model". For my C# project I have been using the namespace Microsoft.Dynamics.SL.ObjectModel from the DLL of the same name. This is described in a PDF "Object Model Reference Guide" and I have assumed it is what is referred to as the "Solomon Object Model" in the list of SL development tools. It looks like it is the most high-level option and it is recommended for "real time data import" in the list of tools. SQLFetch1 is not available via this interface.

Then there is "Solomon Tools For Visual Basic", described in the PDF "Customization Manager Ref. VBA". It looks like this is what you refer to. And you say this interface will work well with C#, for an application that will run un-attended, as a scheduled task? Could you share the names of the DLLS to reference, and the namespace to use?

I have a working application based on the Microsoft.Dynamics.SL.ObjectModel API (so ignoring the SqlFetch1 part you suggest) except it doesn't work as a scheduled task. When run as a scheduled task, the application can't even instantiate the toolbar, I get "Did not receive signal from MSDynamicsSL". The server has all service packs. This could be a more general COM/permission issue and I've debugged it with that in mind, except the error message seems SL specific (I know there are KBs related to it).

Thank you for any additional information you can share on this!
Ram Peru 2,830 on at

Like (0)

Report

RE: How do you do a data lookup using the Object Model? Will be used in data import

You are always welcome Thansen! You can use the C# language for Solomon Object Model(SOM).

We have done this before in C#.
Suggested answer

THansen 95 on at

Like (0)

Report

RE: How do you do a data lookup using the Object Model? Will be used in data import

Thank you again Perumalsamy.

I am using C# and thought the SL Object Model would be my best option- maybe it's not. I'll take a look at the documents you refer to and reconsider the API options.
Suggested answer

Ram Peru 2,830 on at

Like (0)

Report
RE: How do you do a data lookup using the Object Model? Will be used in data import

Hello Thansen,

There is no validation required for the Master records like Account, Sub Accounts, Inventory Items, Projects and Vendors & Customers etc. The screens are under the Maintenance group of each module does not require validation. Below picture is for your reference

The validation is required when you are importing the Transaction records like Sales Order, Purchase Transactions, Time Cards, Voucher, Invoices, Inventory Transactions and Payroll Transaction & Service Module Transactions. The screen are under the Input group of each module requires validation. Below is the picture for your reference.

If you are considering the data import for Voucher & Adjustment entry screen, the Vendor Id must already be existed in SL. Below is the sample validation queries that you need to consider for the data import for the Voucher data import.

Select * from Vendor Where VendorID = ‘XXXX’

XXXXX is the VendorID from your data sheet

Select * from Account Where Acct = ‘XXXXXX’

XXXXX is the Acct from Your data sheet.

Select * from SubAcct Where Sub = 'XXXXX'

XXXXX is the SubAcct from Your data sheet.

You can use the Sqlfetch1 API to retrieve the data from SL database. I would suggest you to go through the CustomizationManagerVBA.pdf & CustomizationManager.pdf which can be found under the Dynamics SL root folder or under Dynamics SL SDK. This above documents will give you the better understanding of API available in Dynamics SL.

Hope this explains
THansen 95 on at

Like (0)

Report

RE: How do you do a data lookup using the Object Model? Will be used in data import

Hi Perumalsamy,

Thank you for your answer!

I am still not clear on this. What do you mean when you say to validate and if the data doesn't exist in SL, skip it? How will I validate if something exists without API functions to perform lookups?

Specifically I need to import sub-accounts. I want to verify if an account is already in SL before I import it. I can do this by traversing all existing data with the first/next methods but that doesn't seem like a good solution, performance-wise. If I enter a duplicate sub-account manually in the UI, the existing sub-account is overwritten, I don't get any warnings. I haven't tried entering a duplicate from code but assume the behavior would be the same. Sub-accounts is an example, there are other types of data to be imported.

I know very little about SL so I'm probably overlooking something basic.

Thanks again for any help you can provide!
Suggested answer

Ram Peru 2,830 on at

Like (0)

Report

RE: How do you do a data lookup using the Object Model? Will be used in data import

Hello Thansen,

To my knowledge, there is no API functions to check the data already exists in SL. You may have validate the data before importing into Dynamics SL.

For instance, you are going to import AR Invoice into Dynamics SL. The following steps need to be considered before start the Object Model.

1. Validate the customer ID before doing the data import. if the customer does not exists in SL. You can skip that data. (Document level)

2. validate Account, Sub Account, Project (if applicable), Task (if applicable) in the Transaction level. if any of the values does not exists in SL, you can skip that data too.

3. you should consider all the required fields on the screen.

you should know the data value against look up & dropdownlist(combo box) before the setting the values into the control using object model. If you set up the wrong value to field, system throws "Item not found(system message 9)" message

Hope this explains.