Database Field Names

From SmartWiki
Jump to: navigation, search

The following lists the database field names associated with Standard Fields.

These are required if updating the database directly (clients with dedicated servers only)
or for Pandora.


  • companyid: Company id. (0 if new company)
  • parentcompanyid: Parent company id.
  • isexternal: Flag (1=external, 0=internal)
  • ownerid: Owner ID of company.
  • contactuserid: Primary contact ID of company. (0 if empty)
  • name: Name standard field.
  • address: Address standard field.
  • address2: Address2 standard field.
  • city: City standard field.
  • state: Province standard field.
  • postalcode: Postal code standard field.
  • country: ID of country field. (0 if empty)
  • phone: Phone standard field.
  • fax: Fax standard field.
  • website: Website standard field.
  • catid: Comma delimited list of category IDs. (Starts and ends with a comma)
  • istaxable: Flat (Only applicable to professional services)


  • ustatusid: Status ID
  • isexternal: Flag (1=external, 0=internal)
  • companyid: ID of user's company. 0 if empty.
  • name: name of user's company.
  • title: Title standard field.
  • uprefix: Prefix standard field.
  • usuffix: Suffix standard field.
  • firstname: First name standard field.
  • lastname: Last name standard field.
  • email: Email standard field.
  • uaddress: Address standard field.
  • uaddress2: Address 2 standard field.
  • ucity: City standard field.
  • ustate: Province standard field.
  • ucountryid: ID of country field. 0 if empty.
  • upostalcode: Postal code standard field.
  • phone: Phone standard field.
  • uphoneext: Phone extension field.
  • rolelist: Comma delimited list of role IDs. (Starts and ends with a comma)
  • isactive: 1 ('Administration or Portal Interface'), 3 ('User Centric or Applicant Interface'), 5 or 0 ('Web Service User','No Access')

Job Costing fields for users

  • isresource:
  • costunit: ID number of cost unit combo-box option.
  • rmargin: Margin field of user/resource.
  • cost: Cost field of user/resource.
  • allcost: Cost field of user/resource.
  • rbillrate: Bill rate field of user/resource
  • resourcetypeid: Resource type ID of user.

Level 1

  • companyid: Company ID of level 1 company/client field. 0 if empty.
  • branchid: Company ID of level 1 branch field. 0 if empty.
  • oppeopleid: User ID of level 1 people field. 0 if empty.
  • opportunitytype: ID of level 1 template.
  • typename: Level 1 template type name.
  • name: Name standard field.
  • description: Description standard field.
  • requirements: Requirement standard field.
  • stageid: Status ID of level 1 status. 0 if empty.
  • statusname: Name of level 1 status.
  • revenue: Revenue standard field.
  • ostartdate: Start date standard field.
  • oenddate: End date standard field.
  • closedate: Close date standard field.
  • probability: Probability standard field.
  • updatedby: ID of level 1 owner.

Level 2

  • description: Description standard field.
  • subject: Subject standard field.
  • eventtype: ID of level 2/3 type.
  • typename: Level 2 type name.
  • objecttype: Parent object type (23=parent object is level 1. 8=parent object is level 2.) *Note: although 18 is the Level 2 Type object type, 8 should be used here.
  • objectid: Parent object ID.
  • assigned: Comma delimited list of user IDs in assigned standard field.
  • contact: Comma delimited list of user IDs in contact standard field.
  • location: Location standard field.
  • startdate: Start date standard field.
  • enddate: End date standard field.
  • span: Duration standard field.
  • eamount: Amount standard field.
  • statusid: Status ID of status. 0 if empty.
  • statusname: Status name.
  • eownerid: User ID of owner. 0 if empty.

Level 3

See Level 2.

Intersection Tables

Level 1 Contact

  • oprid, opportunityid, contactid, roleid

Level 1 Company

  • opcid, opcopportunityid, opccompanyid, opcroleid

Level 2/3 Contact

  • lnkid, lnkeventid, lnkuserid, lnkroleid

Level 2/3 Company

  • clnkid, clnkeventid, clnkcompanyid, clnkroleid


  • afcid, afccompanyid, afccontactid, afroleid

UTA Provider/Consumer (Level 1 to Level 1)

  • conid, conoppid, provoppid, conroleid
  • provoppid: provider Level 1 id
  • conoppid: consumer Level 1 id

UTA Provider/Consumer (Level 1 to Level 2)

  • cevid, cevconoppid, cevprovevtid, cevroleid
  • cevconoppid: Level 1 id (regardless of whether the Level 1 is the consumer or the provider)
  • cevprovevtid: Level 2 id (regardless of whether the Level 2 is the consumer or the provider)

See Also