Filtered tables and search
Hi Quickbase community:
I posted many times recently because I was on a trial period for my organization. We may eventually go with quickbase, but for the moment are on pause with that process.
One of the test users I had review the initial application asked a question which I was unable to answer before the end of the test period. The following is my best to describe the situation.
2 questions actually come out of this description.
I have 3 tables: Locations, Customers, and Users. Each location has many customers, and each location has many users. -- [(Yes, each customer could have multiple locations and each user could have multiple locations, but it doesn't actually make sense to give the customers multiple locations in my situation and adding that bit of complexity about the users doesn't help me ask the question here and won't affect the answer, I don't think)].
I set up a field in USERS where IsCurrentUser = 1 for currentUser and 0 for non-current user. Using summary, formula, and lookup fields this value is passed to the Customers table such that when the customer's location is the same as the current user's location, then a field, say IsAtCurrentUsersLocation = 1 in the Customer table. Every customer with a different location will get a 0 in that field. Thus, each user can have a default view of Customers where IsAtCurrentUsersLocation = 1, meaning they see only customers at their own site.
So, 2 questions arise:
1) when I'm the only user, or there were only a couple of test users, this arrangement works fine. What happens if 2 users from 2 different locations are simultaneously accessing the database? In theory, User A and B at two locations will each tag a location with a 1 and therefore customers at locations A and B will both show up for user A, even though location B would be wrong. Is this indeed a possibility? Or is this really not ever going to show up?
2) Then the question that arose from my test user: he saw a problem when he was viewing the default filtered table which showed only Customers at his current location. He noticed that when using the search function on that page, it was showing him results from all locations, not just the filtered location, let's say it was Michigan. Thus, if we had John Smith in Maryland, John Smith in Michigan, John Smith in Massachusetts, and John Smith, Jr, in Maine, he was going to see all those search results instead of just the John Smith from Michigan. As you can imagine, this would lead to incorrect choices, but he was also concerned about performance.
Any suggestions or thoughts?
Thanks,
Dave
------------------------------
Dave Halter
------------------------------