Associated Builders & Contractors National Export Report – MemberClicks Trade

Each MC Trade client affiliated with the National Associated Builders & Contractors will be required by National to export a report from MC Trade each month, that is then imported into National’s database for dues reporting purposes. This document provides instructions on entering the data into MC Trade for correct reporting, steps to export, and other helpful tips.

Monthly Export: Each month, follow the steps below to export your data to an Excel file that you can then import into National’s database:

In MC Trade, go to Reports > Custom
Select the report titled “Import_Master.rpt”
Enter the start/end dates of the month for which you are reporting and click Accept
Click the “Export” icon in the upper left corner of the MC Trade report:
Choose the location to which you want to save the file (e.g. your Desktop), and in the Save As Type dropdown, select Microsoft Excel (1997-2003) Data-Only (*.xls)
Name the file “Import_Master” and click
Repeat these steps for the report “Import_Trades” with the only difference being the name of the

After you have exported MC Trade's file each month, you will then import that file to National’s database, following their instructions.

Report Fields and Definitions

The National Export Report has 84 required fields, each of which corresponds to data in your MC Trade database. Most of these fields are simple contact information, but several are calculated fields or rely on specific dates in your invoice/payment records, as outlined below.

The report’s filter is simply all profiles that have the “Member” checkbox checked. Payment information will only show for members who have a dues payment with a Payment Date in the date range you select when you run the report.

Field Name	Data Type	MC Trade Data Description
CHAPTER_ID	Number	Your Chapter’s ID number, hardcoded into the report
CO_ID	Number	Profile ID of the member
CO_Company	Text	Report Name of the member
CO_Sort	Text	Sort name of the member
CO_Category	Number	Dues level category number, hardcoded into the report based on the Affiliation Code Type “Dues Level” on the profile Affiliations tab
CO_Type	Text	Chapter, National or Lifetime member type, based on the Member Type affiliation code of the profile
CO_Date_Joined	Date	Member Since date on the Member tab of the profile
CO_Primary_Chapter_ID	Number	The Chapter ID of the chapter to which the member pays National dues, based on the “Primary Chapter” Affiliation Code. This field is blank for National members of your chapter.
CO_Primary_Chapter_Name	Text	The Chapter name of the Chapter to which the member pays National dues, hardcoded from the Primary Chapter Affiliation Code. This is blank for National members of your chapter.
CO_Beam_IND1	Number	The Profile ID of the member’s first Beam Sponsor, listed in the Related Profiles tab as Beam Club Referred
CO_Beam_Name1	Text	The Report Name of the member’s first Beam Sponsor, listed in the Related Profiles tab as Beam Club Referred
CO_Beam_IND2	Number	The Profile ID of the member’s second Beam Sponsor, listed in the Related Profiles tab as Beam Club Referred
CO_Beam_Name2	Text	The Report Name of the member’s second Beam Sponsor, listed in the Related Profiles tab as Beam Club Referred
CO_Mail_Address_1	Text	Address 1 of the Company’s Mailing Address on the Addresses tab (or from the General tab, if there is no Mailing Address)
CO_Mail_Address_2	Text	Address 2 of the Company’s Mailing Address on the Addresses tab (or from the General tab, if there is no Mailing Address)
CO_Mail_City	Text	City of the Company’s Mailing Address on the Addresses tab (or from the General tab, if there is no Mailing Address)
CO_Mail_State	Text	State of the Company’s Mailing Address on the Addresses tab (or from the General tab, if there is no Mailing Address)
CO_Mail_Zip	Text	Zip of the Company’s Mailing Address on the Addresses tab (or from the General tab, if there is no Mailing Address)

CO_Mail_County	Text	County of the Company’s Mailing Address on the Addresses tab (or from the General tab, if there is no Mailing Address)
CO_Street_Address_1	Text	Address 1 from the General tab, if different than Mailing Address
CO_Street_Address_2	Text	Address 2 from the General tab, if different than Mailing Address
CO_Street_City	Text	City from the General tab, if different than Mailing Address
CO_Street_State	Text	State from the General tab, if different than Mailing Address
CO_Street_Zip	Text	Zip from the General tab, if different than Mailing Address
CO_Street_County	Text	County from the General tab, if different than Mailing Address
CO_Bill_Address_1	Text	Address 1 of the Company’s Billing Address on the Addresses tab (blank if there is no separate Billing Address)
CO_Bill_Address_2	Text	Address 2 of the Company’s Billing Address on the Addresses tab (blank if there is no separate Billing Address)
CO_Bill_City	Text	City of the Company’s Billing Address on the Addresses tab (blank if there is no separate Billing Address)
CO_Bill_State	Text	State of the Company’s Billing Address on the Addresses tab (blank if there is no separate Billing Address)
CO_Bill_Zip	Text	Zip of the Company’s Billing Address on the Addresses tab (blank if there is no separate Billing Address)
CO_Bill_County	Text	County of the Company’s Billing Address on the Addresses tab (blank if there is no separate Billing Address)
CO_Phone	Text	Work Phone field from General tab of the member
CO_Phone_Toll_Free	Text	Toll Free Phone field from General tab of the member
CO_Fax	Text	Fax field from the General tab of the member
CO_Email	Text	Email field from the General tab of the member
CO_Web	Text	Website field from the General tab of the member
CO_Flag_Directory_Print	Text	“Y” if member has the Affiliation Code “Print in Directory”
CO_Flag_Directory_Print_Volume	Text	“Y” if member has the Affiliation Code “Print Volume in Directory”
CO_Flag_WBE	Text	“Y” if member has the Affiliation Code “Women Owned Business”
CO_Flag_NAMCA	Text	“Y” if member has the Affiliation Code “NAMCA”
CO_Flag_MBE	Text	“Y” if member has the Affiliation Code “Minority Owned Business”
CO_HUB_Zone	Text	“Y” if member has the Affiliation Code “HUB Zone”
CO_8a	Text	“Y” if member has the Affiliation Code “8a Business”
CO_Type_General	Text	“Y” if member has the Affiliation Code “General Organization”
CO_Type_Sub	Text	“Y” if member has the Affiliation Code “Subsidiary”
CO_Type_Supplier	Text	“Y” if member has the Affiliation Code “Supplier”
CO_Type_Associate	Text	“Y” if member has the Affiliation Code “Associate”
IND_ID	Number	Profile ID of the member’s Main Profile
IND_First_Name	Text	First Name of the member’s Main Profile
IND_Middle_Initial	Text	Middle Initial of the member’s Main Profile
IND_Last_Name	Text	Last Name of the member’s Main Profile
IND_Informal_Name	Text	SalInformal field on the Details tab of the member’s Main Profile
IND_Title	Text	Personal Title of the member’s Main Profile
IND_Prefix	Text	Prefix of the member’s Main Profile
IND_Suffix	Text	Suffix of the member’s Main Profile
IND_Phone	Text	Work Phone field of the member’s Main Profile
IND_Mobile	Text	Mobile Phone field of the member’s Main Profile
IND_Email	Text	Email field of the member’s Main Profile
IND_Mail_Address_1	Text	Address 1 field on the General tab of the member’s Main Profile

IND_Mail_Address_2	Text	Address 2 field on the General tab of the member’s Main Profile
IND_Mail_City	Text	City on the General tab of the member’s Main Profile
IND_Mail_State	Text	State on the General tab of the member’s Main Profile
IND_Mail_Zip	Text	Zip on the General tab of the member’s Main Profile
IND_Mail_County	Text	County on the General tab of the member’s Main Profile
IND_Flag_Do_Not_Mail	Text	“Y” if Do Not Email box is checked on Main Profile’s Details tab
IND_Prior_Beam_Points	Number	Not tracked (0)
DUES_Process_Month	Number	Month of the Payment Date (the month for which the report is run)
DUES_Process_Year	Number	Year of the Payment Date (the year for which the report is run)
DUES_Paid_Date	Date	Payment Date of the dues payment for which the report is run
DUES_Invoice_Year	Number	Year of the Due Date on the invoice to which the payment is applied
DUES_Invoice_Type	Text	Invoice Type, a dropdown field on any invoice of New, Renew, and Adjust. If no Invoice Type is set manually, this field will display as “New” if the DUES_Invoice_Year is the same as the year of the Member Since date, otherwise it will be “Renew.” *
DUES_Category	Number	Dues level category number, hardcoded into the report based on the Affiliation Code Type “Dues Level” on the profile Affiliations tab
DUES_National_Gross_Amount	Currency	Line Item Amount of the invoice to which the payment was applied, for National Dues line on the invoice, prior to any adjustments **
DUES_National_Credit_Amount	Currency	Adjustment amount for the National Dues line item **
DUES_National_Total_Amount	Currency	Adjusted Payment Amount of the invoice to which the payment was applied, for National Dues line items
DUES_State_Gross_Amount	Currency	Line Item Amount of the invoice to which the payment was applied, for State Dues line on the invoice, prior to any adjustments **
DUES_State_Credit_Amount	Currency	Adjustment amount for the State Dues line item **
DUES_State_Total_Aount	Currency	Adjusted Payment Amount of the invoice to which the payment was applied, for State Dues line items
FEA_Date	Date	Payment Date of any Revenue Item with “FEA” in the name, that was paid in the month for which the report was run
FEA_Amount	Currency	Payment Amount of any Revenue Item with “FEA” in the name, that was paid in the month for which the report was run
PAC_Date	Date	Payment Date of any Revenue Item with “PAC” in the name, that was paid in the month for which the report was run
PAC_Amount	Currency	Payment Amount of any Revenue Item with “PAC” in the name, that was paid in the month for which the report was run
TEF_Date	Number	Payment Date of any Revenue Item with “TEF” in the name, that was paid in the month for which the report was run
TEF_Amount	Number	Payment Amount of any Revenue Item with “TEF” in the name, that was paid in the month for which the report was run

* Other than ‘New’ and ‘Renew,’ National requires you to report ‘Adjust’ invoices. If you have reported a member as New/Renew in a previous month, and the member then has subsequent payments in the same year for any reason (partial payments, changing membership levels, increased dues amounts, etc.), they should be reported as Adjust.

** In addition to looking for the pre-adjusted and adjustment amounts, these fields will alternately calculate automatically for second-year prorated invoices using the two billing record amounts set up in the Billing tab of the member, subtracting the full amount from the expiring (prorated) amount to determine the adjustment amount.

Special Data Entry Processes

Setting Invoice Type

One of the columns in the Dues section of the report is to indicate to National the “Invoice Type” associated with the payment you are reporting. The three Invoice types are New, Renew, and Adjust. You can manually set this on any invoice in the Invoice Type dropdown:

“New” should be used for any dues invoice for a new member. “Renew” should be used for any dues invoice for a member who is renewing their membership. “Adjust” should be used for any dues invoice in which a payment is made after the member has already been reported as

“New” or “Renew” in a previous month. If an invoice should be classified as “Adjust” for National, you must set/change the Invoice Type field.

If the Invoice Type field is blank/not set, your Import_Master report will automatically

determine whether the invoice should be classified as New or Renew based on the member’s Member Since date (on the Member tab of their profile). If the Member Since date is in the same year as the dues invoice, it will be “New,” otherwise it will be “Renew.” You only need to set the Invoice Type if you need to override that automated calculation.

Processing $0 invoices for 15 for 12 program members

If a new member dues invoice is processed in October – December, that member will show in the month they pay as a “New” member. National also requires them to show in January of the following year as “Renew” for $0.

When processing the new member, add a second billing record for $0 that expires prior to their next renewal year. For example, if a member joins in October or 2015, you’ll set up two billing records for them:
1. One for the full dues amount, with a Starting year of 2017, no expiration
2. One for $0, with a starting year of 2016, checked to Expire with an expiration date of any time after 1/1/2016
Generate all of these $0 invoices prior to reporting for January. Under Revenue > Auto- Generate Invoices, choose January and remove all but the $0 invoices, then generate. Note the batch
Apply $0 payments to all of these invoices:
1. Go to Revenue > Select
2. On the Batch Numbers tab, enter the Batch Number and click Add, then
3. Right-click in the grid and choose Select All, then right-click again and choose Auto-Generate a Payment Record for all Selected Invoice Line
4. Choose your Accounting Package, Payment Type, and be sure to set both date fields to
5. Click Generate Payment

Creating Billing Records for Members in Proration Period

If a new member joins mid-year and requires a prorated billing record for their second year of membership, then it is necessary to set up two billing records as part of joining the new member.

There are two options for this process, and you will want to select one of the following processes for your prorated billing procedure. The first option is used specifically if it is important for an adjustment to be reflected on the prorated invoice for the discounted amount (see Exhibit A). The second option has less steps involved, but the prorated invoice will not reflect an adjustment. It will just reflect a reduced dues amount due (see Exhibit B).

If it is important that your prorated invoice reflects a discounted adjustment, rather than just a reduced dues amount, then you will choose the first option. If it is not important that an adjustment be reflected within your own records, then you will choose the second option. Your national export will reflect the discounted amount due regardless of the method used, as long as you are consistent in your dues billing procedure.

Exhibit A

Exhibit B

Option 1 – Set Up a Billing Record to Produce a Prorated Invoice with Adjustment

When processing the new member, you will need to create two billing records. The first billing record will be for the prorated billing, and the second billing record will be for the recurring billing amount. Follow these steps to create the billing records:
1. Create a second-year prorated billing record as “Bill Manually”. The prorated billing record should be set to expire on the day before the third year billing record starts (i.e., this would be 12/31 of the year prior to the third-year billing).
2. Create a third-year billing record that reflects the recurring billing
Each year as you auto generate renewal invoices, you will be alerted to the billing items that are flagged as “Bill Manually.” You will then need to manually create the second year prorated invoice for the full amount and then add an adjustment for the appropriate amount to reflect the total cost of the prorated renewal. This will be done for every prorated second-year renewal, every

Option 2 – Set Up a Billing Record to Produce a Prorated Invoice without an Adjustment

When processing the new member, you will need to create two billing records. The first billing record will be for the prorated billing, and the second billing record will be for the recurring billing amount. Follow these steps to create the billing records:
1. Create a second-year prorated billing record that reflects the reduced amount due. The prorated billing record should be set to expire on the day before the third year billing record starts (i.e., this would be 12/31 of the year prior to the third-year billing).
2. Create a third-year billing record that reflects the recurring billing
As you auto generate renewal invoices, your prorated invoices will reflect a reduced amount due and will not reflect an

***Please note that if you are charging both Chapter and National dues to the member, then it will be necessary to create two billing items for the second-year prorated period, and two billing items for the third-year recurring billing, for a total of four billing items.

Articles in this section

Report Fields and Definitions

Special Data Entry Processes

Setting Invoice Type

Processing $0 invoices for 15 for 12 program members

Creating Billing Records for Members in Proration Period