Adding a field to the ac_Products table

[Mike718NY]

If I add a new field to the ac_Products table (Engraving, bit field)
is it possible to add that field to the product edit screen with
the other fields like Name:, SKU:, Price:, etc . .?
I would want to add a Check Box control for the new Engraving field.
I'm guessing I would need the source code to do this.

[jmestep]

No, you don't need source code. You can get code for how to do it on the Able wiki.
http://wiki.ablecommerce.com/index.php/Custom_Queries
Then just save the contents of the field when you click to save the product.

[Mike718NY]

You wouldn't know of any examples?
I wish the Wiki had some because it's not easiest thing to understand.

[AbleMods]

The bigger question is what you want to do with the values once they're entered. If you're looking to use them on the admin side only and/or just display them on the visitor side, you're in luck. Check out this article I just finished this evening: viewtopic.php?f=47&t=12056

If you're wanting the new field(s) to accept input from the customer during purchase and transfer through to the order, product templates is the better way to go. You can set both merchant-only and visitor-side inputs for each product you want when you use product templates.

[Mike718NY]

Check out this article I just finished this evening: viewtopic.php?f=47&t=12056

Thanks Joe for that excellent tutorial. That would solve my problem for any *new* products
added to the website, . . . but I have another problem.

I will be importing data to a new AC7 site with the DataPort and the previous Products table has a boolean "Engraving" field that I need to display in the Admin somehow.

I could import the "Engraving" field into a "temporary holding" ac_Products boolean field
like "HidePrice", but I'm not sure how to transfer it over to a solution like the one give.
Is this possible?

[AbleMods]

It's possible but only with programming code. You'd have to write a program that loops through all the products.

[Mike718NY]

Joe, did you mean using SQL?
This seemed to work:

Code: Select all

INSERT INTO ac_ProductCustomFields
                  (ProductId, IsUserDefined, IsVisible, FieldName, FieldValue)

SELECT ProductId, 0, 0, 'freeship', 'True'
FROM ac_Products 
WHERE HidePrice = 1

[AbleMods]

No, I rarely if ever use direct SQL to manipulate the data tables directly. As a general rule, it's a bad idea because you've skipped all the checks-and-balances incorporated into the data class programming. I use SQL only when I need to repair something in the data itself.

In this case, you could certainly do it as you've already done with a SQL script. But it's risky - none of the data validation built into the API has been called. Thus you risk corruption that may not become apparent until days, weeks or months later.

When I need to do something like that, I'll write a small program that uses the API data classes to pull in the products, populate the necessary sub-class and then call the appropriate .Save() methods. This is the only way to ensure all required data integrity checks are done. ProductCustomFields is a pretty simple table so I'd imagine you're safe, but many other tables are not so simple.

[Mike718NY]

I'll write a small program that uses the API data classes to pull in the products, populate the necessary sub-class and then call the appropriate .Save() methods.

I'm still not that good at using the Able wiki, but your way would be the best.

Do you know if any other tables are used when the "freeship" box is checked
aside from this one:
ac_ProductCustomFields (ProductId, IsUserDefined, IsVisible, FieldName, FieldValue)

I tried the SQL Insert and it worked with the test data I used.

[AbleMods]

No other tables are affected by your addition of the freeship product custom field.