Custom Variable - where (which table) in Access?

[Truska]

Can anyone enlighten me where the data for custom variables is stored in Access.

I can see the variable defined in the variables table but not the actual data anywhere.

We have added a stock due date variable (at product level) which works fine and displays on site etc. but we want to access it from outside Actinic and can not seem to find which table it is in to use it.

sorry this may be obvious by I have looked in numerous tables and can not see it anywhere.

Thanks

[NormanRouxel]

UserDefinedProperties

[Truska]

Norman

Thanks - I was right - it is obvious but I did not look deep enough - was expecting to see data in a column in a table on its own for some silly reason.

I see now I have to use two fields:
nVariableID (as defined in variable table) to identify appropriate records than grab the data from sValue for appropriate product as defined in sContentID.

Should be fun getting query just right.

Anyhow Thanks Norman much appreciated

Regards

[mash]

Hi David,

How did you get on with this:

nVariableID (as defined in variable table) to identify appropriate records than grab the data from sValue for appropriate product as defined in sContentID.

I want to do a similar thing, I have a number of custom variables set up but would like to gain access to them.

Did it work, any advice.

Thanks

[Truska]

Mash

Yes it worked fine

If you have a number of variables you will have to use the query to select the correct variable as all data for all variable and all sections/products etc seems to be stored in this table.

But yes it does and did work - we are now extracting data that is stored in a product level custom variable and using it in non Actinic pages successfully.

This data is also imported into Actinic using hierarchical import - you, in case you need to, can not import to custom variable using the flat file import I believe.

Hope this helps.

[NormanRouxel]

Not sure if this is relevant as these following snippets of code run inside Actinic and query the DB directly.

However they show how to get a Product level variables data when you know the ProductID and Variable name.

Getting User Variable from Database.

Several methods shown.

1)

<actinic:block php="true" > 
	// two step method get UDP 'My_Variable'
	$connect = odbc_pconnect("ActinicCatalog<actinic:variable name="ECMajorVersion" selectable="false" />","","", SQL_CUR_USE_ODBC); 

	$query = "SELECT nID from Variable WHERE sName='My_Variable'";
	$result = odbc_exec($connect, $query); 
	$my_variable_id = odbc_result($result, 1);
	
	$query = "SELECT sValue, nVariableID FROM UserDefinedProperties 
			WHERE sContentID='<actinic:variable name="ProductID" encoding="perl" selectable="false" />' 
				AND nVariableID=$my_variable_id";
	$result = odbc_exec($connect, $query); 
	odbc_fetch_row($result);
	$my_variable = odbc_result($result, 1);

	echo "We got: $my_variable";
	odbc_close_all();   
</actinic:block>

2) Might be slower?

<actinic:block php="true" > 
	// one step method get UDP 'My_Variable'
	$connect = odbc_pconnect("ActinicCatalog<actinic:variable name="ECMajorVersion" selectable="false" />","","", SQL_CUR_USE_ODBC); 

	$query = "SELECT UserDefinedProperties.sValue, UserDefinedProperties.nVariableID FROM UserDefinedProperties 
			INNER JOIN Variable ON Variable.nID=UserDefinedProperties.nVariableID
				WHERE Variable.sName='My_Variable' AND UserDefinedProperties.sContentID='<actinic:variable name="ProductID" enconding="perl" selectable="false" />'";
	$result = odbc_exec($connect, $query); 
	odbc_fetch_row($result);
	$my_variable = odbc_result($result, 1);
	echo "We got: $my_variable";
	odbc_close_all();   
</actinic:block>

3)
Example real world code that gets a group of like named variables of type Filename

	// two step method get LightBoxImage1 .. LightBoxImage10
	$connect = odbc_pconnect("ActinicCatalog<actinic:variable name="ECMajorVersion" selectable="false" />","","", SQL_CUR_USE_ODBC); 
	$LightBoxImages = array();
	for ($i=1; $i<=10; $i++)
		{
		$query = "SELECT nID from Variable WHERE sName='LightboxImage$i'";
		$result = odbc_exec($connect, $query); 
		$variableID = odbc_result($result, 1);
		$query = "SELECT sValue, nID FROM UserDefinedProperties" 
				 . " WHERE sContentID='<actinic:variable name="ProductID" encoding="perl" selectable="false" />'" 
				 . " AND nVariableID=$variableID";
		$result = odbc_exec($connect, $query); 
		odbc_fetch_row($result);
		$lbimage = odbc_result($result, 1);
		$LightBoxImages[] = str_replace('\\', '/', $lbimage);
		}
	odbc_close_all();

[mash]

Thanks, I will have a go and see what happens.

I will let you know how I get on. Take care M

[Seopti]

Hi!

I have a similar problem.
In my access database within the product table I have generated a few new colums, like "author" and "illustrator".
Now I want to show these "variables" above my product description. Is this possible?

Thank you!

[NormanRouxel]

Tom,

There's no need to tinker with the database. See the Starter Guide for a tutorial on creating and using your own Variables.

[Seopti]

Thank you Norman,

this works, if I generate all my new product by hand within the content tab in actinic. Now I have about 2000 products and will get about 2000 new ones, which I have in a table. I already made some tests and I can add them within the acces database (product table) and they are correctly shown in actinic. The problem ist now to add the further information like "author" etc. When I generate variables manually using the library, there are no new columns generated within the database. So it would be fantastic, when they were inserted there. I hope, you can understand the problem, becaue my english isnīt very well. Sorry for that.

[NormanRouxel]

Forget the database. What you're doing there is wrong.

When you create new Variables via the UI they can then be set per Product via the Product Details window. They dont appear in the Product table as they can also be used at Section, Site, and many other places.

See the Starter Guide - as already advised - as this has a complete tutorial.