Community
Showing results for 
Search instead for 
Do you mean 
Reply

Custom product fields linked to PRODUCTID (so they auto fill with PRODUCT NAME)

Copper Contributor
Posts: 20
Country: United States

Custom product fields linked to PRODUCTID (so they auto fill with PRODUCT NAME)

Product sub tables link PRODUCT NAME, ITEM, COST, and PRICE such that they are tied together. When adding a product NAME to an opportunity the ITEM, COST and PRICE fill in automatically. How do I link custom product fields to product name so they fill in similarly? I looked at column properties in SSMS for dbo.TBL_PRODUCT for the 4 linked fields and I don’t see any difference between the in relationships or key/indexes for the linked fields as compared to custom fields. Can the column properties or extended properties be changed or added in SSMS for custom fields so they are linked to PRODUCTID like ITEM, COST and PRICE?
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Custom product fields linked to PRODUCTID (so they auto fill with PRODUCT NAME)

Internally, you have two Product Tables:

 

  1. Master Product Table ... only includes the 4 fields; can import to it with ACT! UI; can't add custom fields
  2. Opportunity Product Table .... linked to Opportunity; can only import via SDK; custom fields

So, the only way I've been able to do that is to create a custom table, linked to the Master Product Table, with the additional fields... then a pretty tricky plugin that detects new Ops and writes the linked data

Employee
Posts: 1,163
Country: USA

Re: Custom product fields linked to PRODUCTID (so they auto fill with PRODUCT NAME)

Hopefully I'm understanding this correctly. Creating a new product, adds that item to the product table where various parameters are set for it such as the name, price, itemID and cost. When a product is added to an opportunity, an OpportunityProduct is created based upon an item from the product table, but now coupled with relative opportunity information.

 

If you're doing this programmatically (creating the product), when you add a custom product to an opportunity, so long as the fields are set, it should populate automatically.

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 20
Country: United States

Re: Custom product fields linked to PRODUCTID (so they auto fill with PRODUCT NAME)

No Matthew, it does not. Say for example you add a custom Product field for “unit of measure” (each, per foot, 12 per case, etc.) to TBL_PRODUCT.  The field does not populate when I enter a product name.  As Mike indicated above, there are 4 fields that are linked to PRODUCTID (NAME, ITEM, COST, PRICE) and these are the only fields that populate automatically. (Try it!)

 

Custom product fields must be entered manually each time a product is added to an opportunity.  Go figure…

 

Mike,

 

What is the master product table called?  Is TBL_PRODUCT the master table?

 

Also, look at the SQL diagram below.  There are a few things that are kind of odd.

 

  1. You would think that PRODUCTID would be a primary key in TBL_PRODUCTSERVICE in the same way PRODUCTSERVICEID is in TBL_OPPORTUNITY_SERVICE. 
  2. There are several fields in TBL_PRODUCT that don’t appear in ACT! Look at the list of fields in bottom screen clipping.  DESCRIPTION, TYPE and PICTURE are among several fields that aren’t in product fields in ACT!
  3. My custom product fields are in TBL_PRODUCTSERVICE.  Shouldn’t corresponding fields also be in TBL_PRODUCT?

 Screenshot ACT!.jpg