[smn198]

I don't know if anyone here had the pleasure of using TIE 1.6 but back in those days, you didn't have refresh time or balanced smartfields. Only query time.

Query time smartfields sit on the target table, and are the quickest type of smartfield to query from (if they are indexed). The problem is that you can only have so many index fields on a table so adding many query time smartfields causes problems. Also, it can become incredibly slow to work with tables with a few hundred indexes on them when updating any data. Therefore, in TIE 1.7 Blackbaud introduced 2 new types of smartfield - refresh time and balanced smartfields.

Both of these smartfields create a field on the target table but this field does not contain any data but rather when referenced runs a function. These are called computed columns as their value is not stored directly on the table, it has to be calculated in some way. In the case of refresh time smartfields, this function runs the criteria of the smartfield on the fly. For complex operations this can be extremely slow but there are times when they make sense. They are called refresh time smartfields as during the refresh they don't need to process. They cannot be enabled or disabled; they are just run on-demand.

Balanced smartfields also run a function when access but this function is used only to retrieve data from a separate table. You need to process the smartfield like the original query time ones but instead of populating the target table directly, these smartfields create their own table. The tables are named like this: <Warehouse prefix>_SF_<Smartfield name>. They contain 2 columns, ID and value. ID links back to the target table and value as you expect is the value you see when you reference it.

If you want to have a delve a little deeper check out the user defined functions named bbfn_<Warehouse prefix>_SF_<Smartfield name> and bbfn_<Warehouse prefix>_SF_<Smartfield name>_T. The function without the _T contains the SQL to calculate which value should be assigned for any given ID. For balanced smart fields this is used to insert the data into the smartfield table. The function with the _T contains the SQL to retrieve the data from the balanced smartfield table. The _T functions are basically lookup functions which are called when you access the field on the target table.

I found when converting smartfield to balanced that some would not work when they had been fine as query time. Blackbaud showed me that this was because with balanced and refresh time smart fields, you cannot reference some functions such as getdate() due to SQL server limitations with functions referencing non-deterministic functions. You can work around these issues by creating a view which contains the SQL select getdate() AS Now and then reference this view instead.

I got a bit carried away with the introduction but my headache is that when you make a change on a query time smartfield, the data in the column is preserved (or at least it used to be – I never used to have this problem in 1.6 when I used smartfields). With balanced smartfields, the table is dropped whenever it is saved and recreated loosing all data. I really wish that the data would be preserved until next time the smartfield was run!

[smn198]

Quote:

Originally Posted by smn198

The function without the _T contains the SQL to calculate which value should be assigned for any given ID.

This no longer the case for some smartfields - expression balanced smartfields seem to only use one function but bin smartfields still use 2. Something changed between when I looked into this and TIE 1.81. The code to populate some balanced smartfield tables seems to no longer be in a function which is good.

Quote:

Originally Posted by smn198

you cannot reference some functions such as getdate() due to SQL server limitations

As the way balanced smartfields are populated have changed, you now can us non-deterministic functions in expression balanced smartfields!