Community
Showing results for 
Search instead for 
Do you mean 
Reply

Access and Sort Custom Tables

Copper Super Contributor
Posts: 478
Country: Australia

Access and Sort Custom Tables

I have been trying to work out how to access and sort a custom table for  some time now, there are several articles here that give you a small part of the picture but nothing substancial or explained.

 

Can anyone assist or direct me to some location with detail, data, or better yet a sample code?

 

My custom table is only 6 columns, i need to access it, sort it by the date column (eg: need all entries for the 2015 year) then total them up

 

Thanks


Jason Dawson Financial Planning
Brisbane | Australia - JDFP Home Page

Act Ver - Act! Premium Version 17.1.169.0, Hot Fix 3
OS - Windows 7 (stand alone machine)
MS Office - Enterprise 2007
Phone - Samsung Galaxy s6
Copper Elite Contributor
Posts: 82
Country: Canada

Re: Access and Sort Custom Tables

Have a look at the sample project here: http://community.act.com/t5/Act-Developer-Downloads/Custom-Sub-Entity-Sample/m-p/307

 

I took a quick look and I believe it shows how to create, access, and sort records.

Ahsan Khalid

Chief Technology Officer
Keystroke Quality Computing Inc.
www.keystroke.ca

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Access and Sort Custom Tables

You can simple use the act OLEDB reader.

 

select SUM(fieldname), COUNT(fieldname)
from CustomTable
where fielddate between '2015/01/01' and '2015/12/31'

 

-- Jim Durkin

Copper Super Contributor
Posts: 478
Country: Australia

Re: Access and Sort Custom Tables

 

Thanks for that

 

I had seen that sample before but discounted since it was 7 years old

 

I have tried to retype it as i know and based on the sample but still get the same problem as it does not recognise the "Commission" name

 

 

        private void countComms(string command)
        {

            Act.Framework.Contacts.Contact c = oApp.ApplicationState.CurrentContact;

            CustomEntityDescriptor descriptor = oApp.ActFramework.CustomEntities.GetCustomEntityDescriptor("Commission");
                        
            if (c != null)
            {
                // Get the CustomSubEntityManager for the Policy entity.  Use the custom type Policy for the data type to be returned from the SDK.
                CustomSubEntityManager<Commission> manager = oApp.ActFramework.CustomEntities.GetSubEntityManager<Commission>(descriptor);

                if (manager != null)
                {
                    // Get all the policies that belong to the current contact.
                    CustomEntityList<Commission> pcomms = manager.GetCustomSubEntities(c, new SortCriteria[] 
                    {
                      new SortCriteria(manager.GetCustomEntityFieldDescriptor("POLICY_EXPIRATION", Act.Framework.MutableEntities.FieldNameType.Alias), ListSortDirection.Ascending)
                    }
                    );

                    if (pcomms != null)
                    {
                        // Add the fields we want to display in the grid to field descriptor collection.
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("POLICY_TYPE", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("POLICY_NAME", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("POLICY_START", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("POLICY_EXPIRATION", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("POLICY_AMOUNT", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("POLICY_NUMBER", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("PRIVATE", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("POLICY_COMMENTS", Act.Framework.MutableEntities.FieldNameType.Alias));
                        //policies.FieldDescriptors.Add(manager.GetRecordManagerFieldDescriptor());

                        // Set the data source of the binding source to be the list of policies.
                        //this.bindingSource1.DataSource = policies;

                        // Databind the grid to the list of policies.
                        //this.policiesDataGridView.DataSource = this.bindingSource1;

                        // Set the last column to fill the rest of the space in the grid.
                        //this.policiesDataGridView.Columns[this.policiesDataGridView.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
                    }
            
                }
             
            }
            



        }

 


Jason Dawson Financial Planning
Brisbane | Australia - JDFP Home Page

Act Ver - Act! Premium Version 17.1.169.0, Hot Fix 3
OS - Windows 7 (stand alone machine)
MS Office - Enterprise 2007
Phone - Samsung Galaxy s6
Copper Super Contributor
Posts: 478
Country: Australia

Re: Access and Sort Custom Tables

Update, i have worded out the problem (i think) after reading a SDK PDF from 7 years ago, it required the "Act.Framework.CustomEntities.CustomSubEntity" or call your own.... some how the samples in other posts fail to tell you that... anyway that appears to work now, so i have moved on to the main reason for the code in the first place

 

I have linekd to the subentity, created a list of the records for the client, now i want to roll through the records and where the entry date matches the record date it will then count the commission column

 

 

                    if (pcomms != null)
                    {
                        int cCountComms = 0;
                        decimal totalComms = 0;

                        string year1 = c.Fields["CUST_ContactTable1_090144.CUST_ActYear_020643759", true].ToString();

                        DateTime startT = Convert.ToDateTime("01/01/" + year1);
                        DateTime endT = Convert.ToDateTime("31/12/" + year1);

                        foreach (CustomEntity e in pcomms)
                        {
                            DateTime cDate = pcomms.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("CommsDate", Act.Framework.MutableEntities.FieldNameType.Alias));
                            decimal cAmount = pcomms.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("CommsAmount", Act.Framework.MutableEntities.FieldNameType.Alias));

                            if ((cDate.Date >= startT.Date) && (cDate.Date <= endT.Date))
                            {
                                cCountComms++;
                                totalComms += cAmount;
                            }
                        }

                        MessageBox.Show("Total number of entries for " + year1 + " is " + cCountComms + ".", oApp.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);


                    }

However these two lines are causing problems it claims its a void and nothing is getting extracted

 

                            DateTime cDate = pcomms.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("CommsDate", Act.Framework.MutableEntities.FieldNameType.Alias));
                            decimal cAmount = pcomms.FieldDescriptors.Add(manager.GetCustomEntityFieldDescriptor("CommsAmount", Act.Framework.MutableEntities.FieldNameType.Alias));

 


Jason Dawson Financial Planning
Brisbane | Australia - JDFP Home Page

Act Ver - Act! Premium Version 17.1.169.0, Hot Fix 3
OS - Windows 7 (stand alone machine)
MS Office - Enterprise 2007
Phone - Samsung Galaxy s6