Basic company search

[knif]

Hi,

I'm just starting with the Act SDK, and I'm trying to insert in companies, which I have figured out. What I want to do now, is to check to make sure that a company isn't already in Act before I add it. I have an extra field (RepFirmID) that stores the value I want to compare it to. All it is a 1-3 digit number that is coming from an existing SQL Server database.

This is what I have so far...(note that "ds" is a dataset object that has the results of my SQL Server query of the information that needs to be transferred)

 Dim fd As Act.Framework.Companies.CompanyFieldDescriptor
            fd = actf.Companies.GetCompanyFieldDescriptor("RepFirmID", Act.Framework.MutableEntities.FieldNameType.Alias)
            Dim sort As New Act.Shared.Collections.SortCriteria(fd, ListSortDirection.Ascending)

            Dim intInsertCount As Integer = 0

            For Each dr As DataRow In ds.Tables(0).Rows
                'Check If RepFirm exists by RepFirmID, if not, then Insert new rep firm in to Companies
                Dim filter As New Act.Framework.ComparisonFilterCriteria(fd, Act.Framework.ComparisonFilterCriteria.Operation.Equals, dr("RepFirmID").ToString)
                Dim compList As Act.Framework.Companies.CompanyList = actf.Companies.GetCompanies(Nothing, New Act.Framework.IFilterCriteria() {filter})


                If compList.Count = 0 Then
                    Dim comp As Act.Framework.Companies.Company = actf.Companies.CreateCompany()
                    'Set Values
                    comp.Update()
                    intInsertCount += 1
                End If
            Next

The problem is that "GetCompanies" is always returning a list of 0 items whenever I use FilterCriteria. I've looked at the forums, and I've seen some suggestions on doing Lookups, but I just can't get my head around them. I don't think this should be a difficult task, so I must be missing something.

[knif · Accepted solution]

Thank you for the update. As I mentioned, I did find another way to accomplish what I need to do with the lookup, but it is good to know that I'm not going crazy because of it not working.

This is what I got to work. (This is in place of the GetCompanies/FilterCriteria code I posted previously)

 Dim lookup As Act.Framework.Lookups.CompanyLookup
                lookup = actf.Lookups.LookupCompaniesReplace(dr("RepFirmID").ToString, Act.Framework.Lookups.OperatorEnum.EqualTo, fd)
                Dim complist As Act.Framework.Companies.CompanyList = lookup.GetCompanies(Nothing)

[mwood]

I think the issue here is that in your filter criteria, your comparing a FieldDescriptor to a string value from your dataset, when we should be comparing the value of the field descriptor to the value from the dataset.

[knif]

Thanks for the reply, and I'll look into that a bit later (it's lunch time ). If I do figure it out, I'll edit this post, in case others could use the info.

After some more searching after submitting the post, I did find a solution. This post helped me out. I guess the method name "LookupCompaniesReplace" threw me off due to the "Replace". I figured it would actually do some replacing of information.

Based on my experience so far in the code, I would highly suggest that methods get the summary information added into the code for future releases (shown below). I don't think I'd be alone in saying that people using the SDK would love you for it.

Public Class Class1

    ''' <summary>
    ''' Divide two numbers and return the result
    ''' </summary>
    ''' <param name="num1">The Numerator</param>
    ''' <param name="num2">The Denominator</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Divide(ByVal num1 As Double, ByVal num2 As Double) As Double
        Return num1 / num2
    End Function

End Class

[knif]

I've done a bit more looking, and I think I'm more confused on the ComparisonFilterCriteria than I was before.

Based on your suggestion, in order to get the value for the field, from my understanding, I'd need to have the company. If I had the company, which I'd probably get via a loop and if statement, I wouldn't need the filter. I'm trying to avoid the looping option.

There are 5 constructors for this object (copied from SDK documentation):

• ComparisonFilterCriteria Constructor(Object,Operation,Object)
• ComparisonFilterCriteria Constructor(Object,Operation,PropertyDescriptor)
• ComparisonFilterCriteria Constructor(PropertyDescriptor,Operation,DateTime)
• ComparisonFilterCriteria Constructor(PropertyDescriptor,Operation,Object)
• ComparisonFilterCriteria Constructor(PropertyDescriptor,Operation,PropertyDescriptor)

Four of these need a PropertyDescriptor object. This is the part that is baffling me, because in order to work, it basically needs the property descriptor.

Any insight that can be provided would be greatly appreciated.

[mwood]

I'm retracting my earlier post, that should work just fine, as I have a sample using FilterCriteria for contacts that works just fine just using the field descriptor and not the value.

This caused me to do a bit more digging on this issue and found there was a defect submitted for exactly this in 11.1. Does this happen to be the version you are using?

In either case I'm investigating the issue in version 13 and 14, and I'll update this thread when I have more information.

[knif]

"Sage ACT! Premium 2011 Version 13.0.401.0, Hot Fix 2"

Copied straight from the About window. If you need any more information, please let me know.

[mwood]

This appears to be a defect, this issue first cropped up in version 11, but was solved in 12 and has somehow resurfaced in 13. I submitted this to be researched yesterday, as I have more information I'll update this thread, but do not have an ETA to resolution as of yet.

For clarity, the issue is if you use the GetCompanies method with FilterCriteria, you will recieve a collection with no companies.

I apologies for the inconvenience.

[knif · Accepted solution]

Thank you for the update. As I mentioned, I did find another way to accomplish what I need to do with the lookup, but it is good to know that I'm not going crazy because of it not working.

This is what I got to work. (This is in place of the GetCompanies/FilterCriteria code I posted previously)

 Dim lookup As Act.Framework.Lookups.CompanyLookup
                lookup = actf.Lookups.LookupCompaniesReplace(dr("RepFirmID").ToString, Act.Framework.Lookups.OperatorEnum.EqualTo, fd)
                Dim complist As Act.Framework.Companies.CompanyList = lookup.GetCompanies(Nothing)