Showing data from a users table based on FileMaker security user?
Hi everyone, I'll try to explain this clearly. My CRM system in FileMaker is fairly standard, has Accounts, Contacts, the normal tables, as well as a Users table. The Users table has things like AccountName, First Name, Last Name, Manager, Department, things like that. The FileMaker usernames under Manage | Security match the AccountName under Users, so that I can do things like when creating a new record, it will auto enter "Get(AccountName)" into the Owner field, which makes them the owner of that account. If they enter list view, it automatically does a scripted search based on Owner = Get(AccountName) and this all works great. (For managers, I have a self join relationship so that users can be related to themselves, or their manager basically).
What I'm wondering though is how would I perform a find, where I want to show something like all accounts where the User that owns them is in the Eastern region, with "Eastern" being the value under Region in the Users table. Like right now the accounts will show region, but it's basically just a non editable field showing that field under account from the Users table based on the relationship such as "Users::Region", so I can't do search or find based on that since the value isn't really on the account level but just showing it as a reference since it doesn't need to be on the account level. I thought of putting the field on the actual account, like Region, so when a record is created it automatically populates with the region from the Users table but that doesn't help if that users region changes later (region is a bad example just using it for reference here but the actual reason I need this is more specific to our organization). Just wondering how most people would handle this. Thanks!