logged in user and tablegrids & Comboboxes

I have a table on my database that has the users location,  the name of the town where the office is located.  The variable is set based on the login name.

I tried putting a empty text field named 'location' on the form and filling it with the variable sLocation and then using '%{location}%'  which works when you fill the table grid with sql - but there must be another method when it is used via the filter setting as it doesn't get replace by the value.

here is an example of my script

var
   sUser: string = '';
   sLocation: string = '';

procedure Settings_OnShow (Sender: string; Action: string);
begin
    Settings.location.Text := sLocation;
    Settings.Button19.Click; //populate Trucking Companies grid with only users location
end;

on the settings form there is as table grid that shows the trucking companies - the table grid settings show all the companies no matter who is logged in or thier "location"  so on the form I put a field text field and fill it from sLocation
Settings.location.Text := sLocation;
and then a button Settings.Button19.Click; that runs a sql query to fill the tablegrid with data filtered by the users locations that it gets from the text field.

SELECT TruckCompanies.id as "id",
       TruckCompanies.Name as "Trucking Company",
       locations.name as "Location"

 FROM TruckCompanies
 LEFT OUTER JOIN locations ON TruckCompanies.id_locations=locations.id
WHERE locations.name LIKE '%{location}%';

this seems like a poor work around your example seems a lot better - I just don't know where to fit it in.

actually I just added the second location in my data and realized  pretty much every tablegrid and every dropdown
will need filtering by the location and the mess I made above isn't going to work.

the requirements are that users form seperate locations shuld not see each others entries, but seperate users from the same location should be able to see each others entries.

and finally an admin should be able to see and extract the data from all locations to be able to run cost reports.

here is my project - it works pretty well for one location as it was originally written - I've started adding the code and ability to have more than one location but with these filtering issues I am in over my depth.

Hello Timlitw,
I didn't realise you'd have so many locations.
So, I've altered my original approach to allow for this;  whatever the user location, the script just reads it and filters a common tablegrid accordingly.   The user only sees the data for their location.  As an administrator, you get to see ALL locations and to maintain the reference tables (passwords, locations etc).
Valid administrator passwords are 'derek' and 'tim'  Valid normal user passwords are 'celtic', 'derry', '100' - but you can see them all in the passwords table if you go in as an administrator.
Because there is hardly any data, I also put the location into the caption at the top of the form (as confirmation that it is switching locations correctly, according to the user you've logged in as).
I think this should address the concerns about the possible number of locations that you have.
Derek.

Hi Timlitw,
I get the relevant user location from the password table using the entered password as the key and store it in a variable (vlocation).
I then pass that location through as a 'real time' filter to the grid with :   
form1.tablegrid1.dbfilter := 'location = "'+vlocation+'"';
It seemed an easy way of automating what you'd otherwise do manually with a 'search' button and a combobox filter.  But because it's done programatically, it stops the user being able to see locations other than their own (unless you're an administrator) yet also allows hundreds of locations to be added without needing any modifications.  And because vlocation is global, the code can be re-used against any tablegrid in your project where you need to restrict location.
So, from what I understood of your requirement, it seemed to offer a 'win-win'.
I downloaded your attachment - it looks an interesting project.
Derek.

That worked great Thanks!

the next step is to limit the comboboxes on the Calculator form

I found that there is an option to fill them with sql.
so I am looking into how this will work

Form1.ComboBox1.dbSQLExecute ('SELECT fieldname, id FROM tablename');

the .dbupdate line errors on my version 3.2  is that a new feature?

this is the sql I need - maybe I'm asking for too much data???

SELECT TruckCompanies.id, TruckCompanies.Name, locations.name
from TruckCompanies
LEFT OUTER JOIN locations ON TruckCompanies.id_locations = locations.id
WHERE locations.name = "Garden City";

at least this gets the correct list when run in SQLiteStudio

this is what I get when I try that code

SELECT id, "TruckCompanies"."Name" FROM "TruckCompanies" WHERE locations.name = "Garden City"

Hi Timlitw,
DBupdate isn't new in itself but it's normally used in relation to a tablegrid update.  I think dbupdate with a combobox must have been introduced with one of the very latest versions.  It's a pity because it's a really easy way to filter a combobox.
When you say that you'd like to filter in a 'smarter way' so that, for example, only products that a trucking company hauls is shown, it sounds very much like the sort of thing that you'd use the parent combobox function for.  It might be easier than trying to script it.
I meant to mention in my last post - your application continues to run in the background when you quit it;  I think it's because the Form1_Onclose procedure in the script has lost its reference back to the Form1_Onclose event in the object properties.
Derek.