Search options (Page 1 of 4)

I'm getting there:

CalcShipping.truckCombo.dbSQLExecute('SELECT TruckCompanies.id, '+
            'TruckCompanies.Name, '+
            'locations.name '+
            'from TruckCompanies '+
            'LEFT OUTER JOIN locations ON TruckCompanies.id_locations = locations.id '+
            'WHERE (locations.name = ''' +sLocation+''')');

what I actually need on the calculator form
there is a place to choose the only the trucking company servicing this location
right now it shows trucking companies from all locations
also the products, the origins, and the destinations also all need to be filtered by the location they are tied to.

Maybe I am approaching this problem from the wrong direction?

I'd actually like to filter in more ways to make the form smarter, once the trucking company is chosen, I'd also like to refilter the output to the product/product type list so that only product the trucking company hauls are shown, and then again when the product has been chosen with only origins that this trucking company service and have this product, and then again only destinations that this trucking company delivers this product to.

and before I go that way I guess I need to check with the user if that logic makes sense.  It might be better to reorder to choose the product first, then filter by locations that can provide that product etc...

ps.. I already have

 1=1 Order By Name asc

in the filter line on the form item so it sorts alphabetical.

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');

Sorry - I thought I had attached my project

I've looked at your sample and Im not sure yet how you made it work that way.

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.

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.

I came across the "search in combo" topic and downloaded the beta version to try it

It is a better solution than what I came up with, Which was a combo hidden behind a text that with an onclick opened a sub form positioned above it that had a field and a table grid to simulate a searchable combo.

the only problem with using the beta is that the generated .exe causes a security warning.   But I can wait to roll out to producton until this is ready.  It will get rid of 4 of subforms I had already used.

Sorry -  I was away from project for a few days.  Thaks for your help!

I had figured out the limit 1, 
I am struggling with how to take that long sql and wrap it into a SQLExecute with the 4 variables needed to get the two results.

Also can I get them both with one call to the database or do I need to run it twice.

here is the project the freight table shows the sql working to filter the list for editing etc.