ACL Tip: Be Careful when Renaming Tables

acl table leggy




When you need to rename ACL tables, be careful to also rename the associated .fil file also.

Otherwise, you (or your ACL script) might get confused. You might delete the wrong table or .fil file, and create a head-scratching problem.

I know because I confused myself.

Background

I have an ACL project that automatically downloads a list of user ID information from a system every week. I format and clean the data, and then create a final table called System_User_IDs.

Because this list is used in multiple ACL projects that run automatically on a schedule, the ACL project that creates the table also copies the .fil file associated with the table (data file) to other ACL projects.

Why I Renamed a Table

This setup worked just fine for a couple months. Then I decided to add some additional fields to the table by joining the original table to another table. The result was a new table (System_User_IDs_Joined) that wasn’t the same name as the original table.

To prevent having to change the table name in the other projects that the table was copied to, I decided to rename the table back to the original name by these commands:


COMMENT--Delete original table name.
DELETE FORMAT System_User_IDs OK
COMMENT--Rename joined table to original table name.
RENAME FORMAT System_User_IDs_Joined System_User_IDs OK

File Won’t Copy after Rename

I then ran the revised script and found that the revised data file (.fil) was NOT being copied to the other projects; instead, I received an error that the file did not exist. What?

Then I realized, after much head scratching, that my copy command copied the System_User_IDs.fil file, but my script had deleted that data file, along with the table, in the DELETE FORMAT command above.

Yes, I have the “Delete data file with table” box checked on the Tools, Options, Table tab. If I had NOT had that checked, the original .fil file, without the new fields, would have been copied. So either way, the result would not have been acceptable.

To fix the problem, I then added the following command (that renames the .fil file) immediately after the other commands shown above:


COMMENT--Rename the joined .fil file to original .fil name.
RENAME System_User_IDs_Joined.fil System_User_IDs.fil OK

Then the table file was once again copied over to the other projects by the copy command (not shown).

Lesson Learned: whenever you change the name of the table, also change the .fil file associated with the table–regardless of whether you copy the table to another project.

————-
Why did I choose the table picture at the top of this post to illustrate this post? First, I like the unexpected, especially when it can also be humorous (at least in my mind).

Second, I didn’t think an icon showing a real ACL table would invite you to sit down and read this post like a real table would.

Third, I wondered what an ACL table would look like if it WAS a real dinner table. Well, I think it would look similar to the picture above. Kind of like a regular table, but with funky legs. At first glance, the table leg on the right looks different, but then you realize that it’s the same as the other legs, but the angle of the picture makes it look odd. In my head, that’s an ACL table; it works, but it isn’t always what you expect it to be. :)

Coming Next

In my next post, I describe how to copy tables created in one ACL (main) project to another (secondary) ACL project. I also describe what you need to do in the secondary project whenever you change the fields in the main project (even if the table name and .fil doesn’t change).

Are you wondering what picture I use to illustrate the next post?

Leave a comment

Filed under ACL, Audit, Data Analytics, How to..., Scripting (ACL)

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.