What's the purpose of the Active Users table?
How, and under what circumstances, do you add a record for an Active User?
Off hand, I can't see a reason for having both tables. You can just add a status field to AllUsers, ActiveStatus.
If a user is active, set ActiveStatus to 1. If a user becomes inactive, clear the field (set it to "" ).
in a layout of active users you can place the related fields All users::fieldname directly on your layout to display the related information.
Phil in some large databases or in an application where processes loop through "active users" doing the extra find or processing could really hurt performance. Especially if the active subset is significantly smaller than the whole.
In general you are right. This is a case where Id really like to have a constant in my relationships.
You'll note that I asked this as a question.
It would take an extremely large table of users (1,000's) before a search of the table would produce a noticeable delay and I don't imagine any "looping" would be required. A simple find to match the Get ( AccountName ) or Get ( UserName ) should suffice.
Though I agree with you, for looping I was refering to other uses of the table within the application where you would loop through the found set and do something.
I was just pointing out the rational that might lead someone to do that. Performance being the only real reason I can see (and even thats a bit of a stretch) Ill be quite now :)
Thanks guys for your help!
What I neglected to mention is I'm doing this for data migration not storage. Phil, if I were storing the data you are right I wouldn't need the second table.
I have a CMS system that has all the users who have purchased from this client, that's the All Users list I referred to above. Then on the bulk email system a subset of those users have opted into a list to get updates on their products. So what I have are 2 CSV lists from the 2 systems. I needed a way to find those users who had opted into thist email list and get their detail information from the other list. Importing the detailed All Users list would add users who had not opted in for those e-mails. Hope that makes it clearer.
What I stumbled across last night was probably what you've suggested aammondd.
I created a layout that looked basically like:
ActiveUsers::Email | AllUsers::Email | AllUsers::Detail1 | AllUsers::Detail2 | AllUsers::Detail3
I then sorted the view by Activeusers::Email and when I ran out of Active Users, I had my subset. I then was able to save that subset as a CSV and import it into the Email system.
Not very elegant, and probably not the best way to do it, but it was the best I could come up with and it seems to have gotten me the results I needed.
If anyone has a better way to do this, I would love to know as I will have this problem again in the future for this and other clients.
Thanks everone for your help!
Since you have your link set, a number field would be better since you could then change the email address without breaking the link, you can do a search in a parent file based on the records in a child file.
Now, to answer your question: there are two methods.
A) create your found set in the active users table and then user Go To Related Record in the all users table. This should list the matching records if you use the proper options.
B) Add a constant field in your all users table, calculation = 1. Now the secret until now method of finding parent records via a related search:
Go to layout parent
enter find mode
set field childfile;constantfield = 1
This is easier than goint to the child file, doing the search and then doing a gttr but you need that constant field for your question. You can also use this to find by name, etc of your active users.