To ease that pain, I wrote myself a little procedure that will generate a view for JIWA custom data that will allow you to write simple SQL queries against it. Or to allow you (e.g. from within a plugin) to easily get all the custom data for a business object with a single query against the database.
It's imperfect, because it doesn't look at the plugin id, but I always make a point of making the setting names unique across plugins (for a given business object), so it works for me.
I've implemented it as a stored procedure. Even though I'd only use it a couple of times when doing a new implementation, saving it as a stored procedure means it's already there if I need to do it again later.
You have to provide values for:
- @settingTableName - the name of the settings table. e.g. IN_CustomSetting
@valuesTableName - the name of the related values table e.g. IN_CustomSettingValues
@keyFieldName - the name of the business object key field within the setting table. e.g. InventoryID
@viewSchema - the schema to use for the new view. e.g. 'dbo'
@viewName - the name of the new view - e.g. IN_CustomData
the stored procedure :
- Code: Select all
CREATE PROC [AERP_GenerateCustomDataView](@settingTableName sysname, @valuesTableName sysname, @keyFieldName sysname, @viewSchema sysname, @viewName sysname) AS
begin
declare @sql nvarchar(MAX) = N'SELECT @sql2=N''CREATE VIEW ' + QUOTENAME(@viewSchema) + N'.' + QUOTENAME(@viewName) + N' AS
SELECT
' + QUOTENAME(@keyFieldName) + N''' +
replace((SELECT ''
,'' + quotename([SettingName]) + '' = max(CASE WHEN RTRIM([SettingID]) = '''''' + RTRIM(SettingID) + '''''' THEN Contents ELSE '''''''' END)''
FROM ' + QUOTENAME(@settingTableName) + N'
ORDER BY DisplayOrder
FOR XML PATH('''')
), ''
'','''') + N''
FROM ' + QUOTENAME(@valuesTableName) + N'
WHERE COALESCE(Contents,'''''''') <> ''''''''
GROUP BY ' + QUOTENAME(@keyFieldName) + N'
'''
--print @sql;
declare @sql2 nvarchar(MAX);
execute sp_executesql @sql, N'@sql2 nvarchar(max) OUTPUT', @sql2 OUTPUT;
print @sql2;
execute sp_executesql @sql2;
end
This should work on any version of SQL that has nvarchar(MAX) support (2005+ IIRC).
calling it:
- Code: Select all
EXECUTE [AERP_GenerateCustomDataView]
@settingTableName = 'IN_CustomSetting'
,@valuesTableName = 'IN_CustomSettingValues'
,@keyFieldName = 'InventoryID'
,@viewSchema = 'dbo'
,@viewName = 'IN_CustomData'
The view it generates will look like a bit like this, based on a technique I learnt from SqlServerCentral.com. (this is for the JiwaDemo database) :
- Code: Select all
CREATE OR ALTER VIEW [dbo].[IN_CustomData] AS
SELECT
[InventoryID]
,[product_id] = max(CASE WHEN RTRIM([SettingID]) = '257c204d-240d-4b33-8b1d-436e6f300c11' THEN Contents ELSE '' END)
,[ComboBox] = max(CASE WHEN RTRIM([SettingID]) = '1' THEN Contents ELSE '' END)
,[Sample Custom Field 1] = max(CASE WHEN RTRIM([SettingID]) = '8d0a674cbb9d4e7995c5' THEN Contents ELSE '' END)
,[Discontinue Date] = max(CASE WHEN RTRIM([SettingID]) = 'accaafadf2744904bbe9' THEN Contents ELSE '' END)
,[WebBrowserURL] = max(CASE WHEN RTRIM([SettingID]) = 'b450e24b-0b83-4650-b812-311f26dfeeb2' THEN Contents ELSE '' END)
,[Date Field] = max(CASE WHEN RTRIM([SettingID]) = '2' THEN Contents ELSE '' END)
,[CheckBox] = max(CASE WHEN RTRIM([SettingID]) = '3' THEN Contents ELSE '' END)
,[Sample Button] = max(CASE WHEN RTRIM([SettingID]) = '4' THEN Contents ELSE '' END)
,[Sample text] = max(CASE WHEN RTRIM([SettingID]) = '5' THEN Contents ELSE '' END)
,[Sample Integer] = max(CASE WHEN RTRIM([SettingID]) = '6' THEN Contents ELSE '' END)
,[Sample float] = max(CASE WHEN RTRIM([SettingID]) = '7' THEN Contents ELSE '' END)
FROM [IN_CustomSettingValues]
WHERE COALESCE(Contents,'') <> ''
GROUP BY [InventoryID]
a query would just be like querying a table:
- Code: Select all
SELECT [InventoryID]
,[product_id]
,[ComboBox]
,[Sample Custom Field 1]
,[Discontinue Date]
,[WebBrowserURL]
,[Date Field]
,[CheckBox]
,[Sample Button]
,[Sample text]
,[Sample Integer]
,[Sample float]
FROM [JiwaDemo].[dbo].[IN_CustomData]
From there, you can manually modify the view to:
- change field names
- convert data types - JIWA stores everything as text, but you can convert to whatever data type you want in the view. (the procedure could probably do this, but I seldom have a need for non-text versions of the data when it comes to custom data, so I haven't bothered, and it's trivial to do on the view itself where necessary).
P.S. this kind of view will perform better if your clustered index is on a combination of business object rec id and setting id. e.g. InventoryID, SettingID. This is because it will have all the related records on the same page in SQL (or at least on very few pages).