Community
Showing results for 
Search instead for 
Do you mean 
Reply

There has to be a better way to query between dates.

New Member
Posts: 8
Country: USA

There has to be a better way to query between dates.

[ Edited ]

So currently I'm trying to get a list of contacts that we have charged their card between to dates. Now when I charge the card I save in a field "CDATE" with the date M/d/yyyy

 

currently I have to do multiple foreach loops to search for contacts charged on a single date, then I take those contacts ID and add it to a list. Once done I research by ID and return that to my program to do what It needs.. The problem is it takes about 15 seconds per date to search. So as you can imagine if I run the report for a months period it will take some time.

 

is there any way I can pass an array to the criteria column and have it do the work?

 

 ContactFieldDescriptor mefd = GetCFD(CFD);
        ContactList contactList;

        List<Guid> guids = new List<Guid>();

        DateTime startDate = DateTime.Parse(date1);
        DateTime endDate = DateTime.Parse(date2);
        foreach (DateTime date in GetDateRange(startDate, endDate))
        {
            System.Diagnostics.Debug.WriteLine(date.ToString("M/d/yyyy"));
            CriteriaColumn column1 = FW.Lookups.GetCriteriaColumn(mefd);
            CriteriaColumn column2 = FW.Lookups.GetCriteriaColumn(GetCFD("cdate"));
            //create intividual criteria items using criteria columns
            dynamic cList = new List<Criteria>();
            Criteria c1 = new Criteria(LogicalOperator.And, 0, 0, column1, OperatorEnum.Contains, data);
            Criteria c2 = new Criteria(LogicalOperator.End, 0, 0, column2, OperatorEnum.Contains, date.ToString("M/d/yyyy"));
            cList.Add(c1);
            cList.Add(c2);

            //convert the list to an array
            Criteria[] cArray = cList.ToArray();

            ContactLookup contactLookup = FW.Lookups.LookupContactsReplace(cArray, true, true);

            contactList = contactLookup.GetContacts(null);

            foreach (Contact c in contactList)
            {
                System.Diagnostics.Debug.WriteLine("found one");
                guids.Add(c.ID);
            }
        }

        contactList = FW.Contacts.GetContactsByID(null, guids.ToArray());

        return contactList;

I would love to be able to put the C2 column to an array and have it see if any values in the array are found but I was getting a null value.

 

and yes I have tried to use the enum Between but it says the field is not able to be searched between.

 

 

thanks ryan