Always looking for ideas for my blog, I could not resist when Chris Broussard asked me to write something on ACL workspaces.
Simply put, an ACL workspace is used define either physical fields or expressions. However, instead of defining these fields in the table layout, the definitions are stored in a separate project item (i.e. a workspace). The main advantage is that the workspace can be shared by multiple data files. They cal also be exported to a separate physcial file and imported into other projects.
From ACL Help:
An ACL workspace is an ACL project item that contains one or more field definitions that have been saved for reuse with other tables. When a workspace is activated, the fields within it are available to the current table. Workspaces let you maintain and reuse definitions of physical fields, computed fields, or filters (which can be selected from the Filters list in the Expression Builder). Reusing or sharing field definitions and filters ensures consistency, and reduces the repetitive work of defining fields and filters that are used in more than one table.
Workspaces can be shared between tables that contain fields of the same type, with the same names. For example, you might want to associate a workspace with successive tables of a given type, such as accounts receivable tables from different periods, or from different divisions of the same company.
If you are working with multiple-record-type files, you can store the definition for each record type in a separate workspace. You can select the appropriate workspace to process records of a specific type.
Why use Workspaces
The main reason I use workspaces is to make the management of expressions, which change and are used by many files, easier. For example, I have a workspace that has the Exchange rates for the month. I have multiple data files that need the exchange rates. These files are downloaded monthly and I re-use the table layouts. If the expressions were stored in each table layout, I would have to change every table layout each month. With a workspace, I can make one change and it is reflected in all other tables when I activate the workspace.
Off the top of my head, here are other areas where I have used workspaces:
- P-Card definition of “questionable” expenses. As I learn about (or discover) more abuse/fraud, I update the filter which I can apply to current and future P-Card data files.
- Tax rates by state. When these change, I update the main workspace and am good to go.
- Overtime rates based on job category and level
The second reason why I use workspaces is that I support many users. I extract detailed transactions from multiple files every month (e.g. all financial transactions from SAP – BSEG table). When an audit is initiated, I extract the transactions that pertain to the audit and give the data and table layout to the auditors for further analysis. The extract table has the same layout as the main data file. Expressions are stored in a central workspace file to which all auditors have read access. This means that I can update any expression in the workspace and the auditors have the new definitions.
I realize that some of my examples could be performed with Relate (e.g. State (Parent) to State tax rate (Child). Therefore, I could just update the child table. I do this for many things such as table look-ups (e.g. link GL on a detailed transaction to the GL master file which has the GL description). But sometimes conditional expressions are easier to maintain and modify that using Relationships.
There is one caveat (warning), the expressions in a workspace are only temporarily added tot eh table layout. This means that you have to activate the workspace each time you open table. This is why you get the latest version of the expression. However, if you change the table layout while the workspace expressions are being used – then the expressions are permanently attached to the table layout. I actually use this to ensure that workspace expressions don’t get updated when I have a file that I want to remain static. Within a script: I open a table and activate the workspace. This loads expressions into table layout temporarily. Then I define a field temp1 = “1” and close the file. This saves the revised table layout including the workspace expressions. Then I open the table and delete temp1.
I hope this has given you a better idea of the power and utility of workspaces.
This article has 4 Comments
Great information, Dave.
What happens if you open a table, activate a workspace, and change the table layout while the workspace expressions are being used (thereby making the expressions permanently attached to the table layout).
Then you….Update the expressions in the central workspace file, and open the same table a few days later, and activate the same workspace?
I’m guessing the expressions would be those definitions from the permanent table layout? Would the expressions from the Workspace ignore completely? How would you know if expressions in a workspace already exist in a permanent table layout?
When you activate the workspace you will be prompted you to see if you want to replace the existing expression(s). However, – the tricky part – unless you save the table layout – the new expression is not saved (only temporary) and the old expression remains.
How do you save the table layout after activating the workspace?
Can you please explain why and how do you define a field temp1 =”1″ then close it? After you open the table, do you get a revised table with teh change made by activated workspace. I am bit confused on the following “Within a script: I open a table and activate the workspace. This loads expressions into table layout temporarily. Then I define a field temp1 = “1” and close the file. This saves the revised table layout including the workspace expressions. Then I open the table and delete temp1”.
By defining a field (Temp1 = “1”) I am forcing ACL to save the table layout which contains the workspace field(s). If I don’t force the layout to be saved, when you close the table, the workspace field(s) will be lost. However you can always ACTIVATE the workspace each time you need it.