Where in the database can I find the actual calculation for a field? I'm trying to pull (via SQL) a list of Module Name, Field Name, Calculation for every calc'd field (specifically for Control Standards but will be used elsewhere too.) I've looked in all the tblIVFieldDefxxx tables but don't see what I need. Has anyone done this and if so can you share the SQL?
Archer Version 5.5 SP2 -
- Community Thread
- Forum Thread
- RSA Archer
- RSA Archer Suite
- Tips and Tricks
Thanks Scott - that's super helpful, I was able to write the query. Unfortunately I didn't realize that the calcs used field IDs in the backend. Makes total sense, just didn't realize that additional translation was required (and not really possible via SQL.) Still a huge help though - thanks again.
This doesn't rebuild the formula to the state in which you'd see it in the UI, but it's as close as I was able to get only using SQL to do it. Hope it works for you as well. If you're on one of the later versions of the platform with added multilingual, you'll need to add in the appropriate translation tables for Modules, Levels, etc.
Thanks Scott! Looks like my version does have tblModuleTranslation so I'll try adding the joins to that and then give it a shot. Thanks again!!
Added translation tables for Module and Level. Works perfectly. Still need to do the lookups for referenced fields and values but this at least gives me all the calcs in a single result set which is a huge help. Thanks again.