Associated Builders & Contractors National Export Report

 

Each WebLink client affiliated with the National Associated Builders & Contractors will be required by National to export a report from WebLink each month, that is then imported into National’s database for dues reporting purposes. This document provides instructions on entering the data into WebLink 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:

  1. In WebLink, go to Reports > Custom
  2. Select the report titled “Import_Master.rpt”
  3. Enter the start/end dates of the month for which you are reporting and click Accept
  4. Click the “Export” icon in the upper left corner of the WebLink report:




  5. 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)
  6. Name the file “Import_Master” and click
  7. Repeat these steps for the report “Import_Trades” with the only difference being the name of the

After you have exported WebLink’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 WebLink 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

WebLink 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.

  1. 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
  2. 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
  3. 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

  1. 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
  2. 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

  1. 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
  2. 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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.

Want some training on WebLink Connect? Sign up today for one of our upcoming online training classes.