Using an "IsVisible" field (1 Viewer)

[RossWindows]

Do you find it typical to include a field called something like "IsVisible" to most of the tables in any given database, then create a corresponding query for each of the tables which includes all fields where IsVisible = true, then for anything you create in the future, you base it off the IsVisible query?
Hope that made sense.

So for, let's say, tblEmployees, you would create a query called qryEmployees where IsVisible = true, then basically you would never build another query based on tblEmployees, but instead base it on qryEmployees.

In your opinion, is this something you would typically do? Always? or only as needed?

 

[boblarson]

Actually, it is a reasonable thing to do. I don't know about the IsVisible but I have done it where you have a field called IsActive for employees and then do not ever delete them, just uncheck the box. For normal tables a field called Archived can be good so that when you check that box it disappears from your main queries.

We do that essentially with our SQL views here where I work. We use mostly SQL backends here.

 

[RossWindows]

That's what I was thinking. For the purpose of preserving data, never having to delete records, I figured it was reasonable. I have found it beneficial to automatically include CreatedBy & DateCreated to almost every table I create. This helps with tracking and so forth. Adding those fields is almost second nature to me, and I was wondering if I should start getting in the habit of including, as a rule, some type of marker like "Active" "Archived" or "Visible" to every table I create. My thoughts were, since it would be a boolean field, it wouldn't consume a lot of space, so I figure it couldn't hurt, but do you think it's a little overkill?

 

[boblarson]

but do you think it's a little overkill?

Not according to Access MVP Armen Stein who said almost the same thing at our Portland Access Users Group meeting last week. I haven't been in the habit of doing this, but I do think it is a good method.

 

[gemma-the-husky]

i would have thought in an complex system its the sort of thing that could become very useful, and never hurts to have anyway. even if you dont bother activating it.

i certainly use an "active" flag to help limit items in combo boxes etc.

 

[RossWindows]

...its the sort of thing that could become very useful, and never hurts to have anyway. even if you dont bother activating it...

That, right there, explains it all IMO.

Thanks for your input guys!

 

[boblarson]

i certainly use an "active" flag to help limit items in combo boxes etc.

That reminds me of one other thing Armen suggested. He suggested including the Inactive records at the bottom of your combo boxes and prefacing the text with an asterisk (*) to show the users that they are inactive. The reason being, is that if you go to a record where someone, say an employee, had a sale and was listed as the sales person and then you do not show inactive employees, their ID is still in that field but when you go there they will show up as a blank. So, including the inactives, but in a different way, will allow people to see who was on that record but yet know immediately that they were inactive.

And if you sort the list by Active /Inactive (-1 for Active, 0 for Inactive) and then by name, then it will sort the inactives to the bottom of the listbox or combo box.

 

[Galaxiom]

prefacing the text with an asterisk (*) to show the users that they are inactive.

I have also seen this done. Remember too that the asterisk is easily added to or removed from the field with the AfterUpdate event procedure of the checkbox used to set the Active/Inactive status.


I always have an Include field in combobox row source tables. Users have often indicated they don't need a particular selection that I have included because my data analysis suggested it would be required. Saves arguing the point at the time and they are easily satisfied by unchecking the box.

More importantly it is easily restored later when they eventually realise it was needed after all. It saves having to put it all back in place and you never feel like having to say "I told you so."

In many cases I also include a SortOrder field so the items appear in a logical sequence when alphanumeric is not really appropriate.

 

[boblarson]

I have also seen this done. Remember too that the asterisk is easily added to or removed from the field with the AfterUpdate event procedure of the checkbox used to set the Active/Inactive status.


I always have an Include field in combobox row source tables. Users have often indicated they don't need a particular selection that I have included because my data analysis suggested it would be required. Saves arguing the point at the time and they are easily satisfied by unchecking the box.

More importantly it is easily restored later when they eventually realise it was needed after all. It saves having to put it all back in place and you never feel like having to say "I told you so."

In many cases I also include a SortOrder field so the items appear in a logical sequence when alphanumeric is not really appropriate.

In my example (Armen's example, really) the asterisk is NOT in the data. It is just concatenated on to the name in the combo's query. It does not require data to be changed.

 

[Galaxiom]

Good point Bob. In the db I saw, the asterisk in front of the name actually was the indicator instead of having an active field. I remember seeing it in the table and tried simply putting an asterisk in front of a name to confirm it.

Perhaps they saw Armen's example and misunderstood the idea.

Then again I suppose there could be an argument that it is more efficient. The inactive status would be automatically shown at all times. In this case the inactives were supressed from the login dropdown so it would have to be parsed that once.

Using Armen's technique would require testing the status and concatenating the asterisk every time an invoice was displayed.

 

[boblarson]

Yes, it takes a little processing power but an IIF isn't that bad. And, what if someone decides to type an asterisk at the beginning of one. If it shows up in the Active list, it is very obvious there is some error somewhere. If it shows up in the Inactive list below it is obvious something was up as it would have TWO asterisks.

So, I think I'd still go with the Active field and concatenate the asterisk on. What if you have any place where you DON'T want the asterisk to appear? Then it would take something to split that off and I would rather join than split.

 

[Galaxiom]

I agree. It is rarely worth doing funny business to save a field and a little bit of processing.

 

[SOS]

I've done similar things in the past and it works great but you have to educate your users a little so they don't get confused. Otherwise it really makes things easy (using an Active flag) because if you need something to INCLUDE the inactive data you can just create a query that does. Very, very simple.

 

[gemma-the-husky]

actually, getting inactive items sorted to the bottom is a great idea. wish i'd thought of ithis myself

one problem with ignoring inactive records in combo boxes etc, is that you need to distiguish between

a) entering a new record - when you only need to see the active records, and
b) reviewing an old record - when you still need the old data

and this method is much easier than trying to change a combo boxes record source depending on whether you have a new record, or an old one.

Definitely a good tip for the future.

 

[Kryst51]

I have been watching this conversation with interest. This is definitely something to include for the future. I also like the idea of including the inactive ones at the bottom, I had not thought of this before, and it would have been helpful to know when I was designing my db, As salesmen have left our company to not have records corrupted, or unviewable etc. Thanks for starting this conversation RossWindow.

 

[Fifty2One]

For some records I have used a date stamp as the active/inactive flag. Occasionally I use the checkbox but prefer to use a datestamp as memory is cheap. These records can always be passed to an archival database based on the datestamp if there is sufficient data to make the core storage into a lumbering giant.
If the IIF statement is sucking the response time from your application then you need to address the structure of the application. I do a lot of developing on older machines to take advantage of the speed and storage improvements of a clients setup and network. If a few P4-2Ghz on an old router can perform at a resonable response time I know it will run at a good speed on a clients network.