Search options (Page 2 of 18)

Hello sambatyin and welcome on the forum,

Currently, MVD can only use sqlite database (stored by default in the application folder) or connect to a remote MYSQL Database.
I am not sure whether or not Dmitry plans on offering other database support.

As for the scripting language used, this is Delphi (Object Oriented Pascal). Not the most recent language of all, but strongly typed and structured, and fun to use because, after all, that's what is important right ?

You'll find plenty of tutorials and examples on the forum, and do not hesitate to ask for help.

Cheers

Mathias

Hello gonpublic2k,

You are right, sometimes, the simple "Report" button is not enough and you need more flexibility on the data you send to the report.
You have to more options at your disposal to do so.

1 - The first one you already known : it's the "Report SQL" button.
With this option, you can write the query you want and add all the joints and calculated fields you need, cases and so on.
The main problem with this approach being that a complex query will narrow down the data you get in return.

Look at the query bellow :

SELECT DISTINCT
asset.asset_sku,
asset.asset_name,
asset.asset_price,
asset.asset_status,
keyword.kw_text
FROM
asset
INNER JOIN asset_kw ON asset_kw.id_asset = asset.id
INNER JOIN keyword ON asset_kw.id_keyword = keyword.id
ORDER BY keyword.kw_text,
CAST(asset_sku AS INTEGER) ASC  

This query returns every asset in my database as long as they are linked with a keyword (or more) and prints out a report.
(I have 3 tables : a table called asset, a table called keywords and a joining table called asset_kw in between)

The INNER JOIN instruction does filter a lot the result of the query because it's result is exclusive. All my assets that are not linked with a keyword will not show up in the report. If I want them all, I need to replace the INNER JOIN with LEFT JOIN. This way, all assets are reported, and those having an assigned a keyword will show this extra information.

As for calculated fields, they are added the same way as you did them in you database except the come directly in the query. If I add

(SELECT COUNT(id_keyword) FROM asset_kw WHERE asset_kw.id_asset = asset.id) AS NBK    

in my query, I have one more result column in my report counting how many keyword has been assigned to each asset.

But sometimes, one query for too many tables is too complicated to write and you can not link all needed tables with JOIN and so on.

2 - Then you can simply write you queries in script and send them to the report.
Add an Onclick event to a button and use datasets.

You will need two different kind of datasets, the regular one you probably already use, and the ones for the reports.

Here is an example on how you could send the results of 3 different queries to a single report :

procedure Form2_Button1_OnClick (Sender: string; var Cancel: boolean);
var
    frxDBDataSet1, frxDBDataSet2, frxDBDataSet3 : TfrxDBDataset;
    Results1, Results2, Results3 : TDataSet;
    asset_id : Integer;
begin
    //the variable I need to trigger the queries
    asset_id := mark_list.TableGrid1.dbItemID;
      
      //qyery one stored in dataset1
      SQLQuery('SELECT '+
                'asset_sku,'+
                'asset_old_sku,'+
                'asset_name,'+
                'asset_url,'+
                'asset_price,'+
                'vendor_name,'+
                'asset_status,'+
                'asset_detail '+
                'FROM '+
                'asset '+
                'INNER JOIN vendor ON asset.id_vendor = vendor.id '+
                'WHERE asset.id = "'+IntToStr(asset_id)+'"', Results1);

        //query two stored in dataset 2
        SQLQuery('SELECT '+
                 'asset.asset_sku,'+
                 'asset.asset_name,'+
                 'asset.asset_status '+
                 'FROM '+
                 'asset '+
                 'INNER JOIN asset_req ON asset_req.id_asset1 = asset.id '+
                 'WHERE asset_req.id_asset = "'+IntToStr(asset_id)+'" ORDER BY CAST(asset_sku AS INTEGER) ASC ', Results2);

      //query three stored in dataset 3
      SQLQuery('SELECT '+
                'artist.artist_name '+
                'FROM '+
                'asset '+
                'INNER JOIN artist_asset ON artist_asset.id_asset = asset.id '+
                'INNER JOIN artist ON artist_asset.id_artist = artist.id '+
                'WHERE artist_asset.id_asset = "'+IntToStr(asset_id)+'"', Results3);

      
    //transfer standard datasets results to report datasets
    frxDBDataSet1 := TfrxDBDataset.Create(Form2);
    frxDBDataSet1.UserName := 'Asset';
    frxDBDataSet1.CloseDataSource := True;
    frxDBDataSet1.OpenDataSource := True;
    frxDBDataSet1.DataSet := Results1;

    frxDBDataset2 := TfrxDBDataset.Create(Form2);
    frxDBDataset2.UserName := 'Reqs';
    frxDBDataset2.CloseDataSource := True;
    frxDBDataset2.OpenDataSource := True;
    frxDBDataset2.DataSet := Results2;

    frxDBDataset3 := TfrxDBDataset.Create(Form2);
    frxDBDataset3.UserName := 'Artists';
    frxDBDataset3.CloseDataSource := True;
    frxDBDataset3.OpenDataSource := True;
    frxDBDataset3.DataSet := Results3;


    //assign the three report datasets to the same report
    Form1.frxReport.Clear;
    Form1.frxReport.DataSets.Clear;
    Form1.frxReport.DataSets.Add(frxDBDataSet1);
    Form1.frxReport.DataSets.Add(frxDBDataset2);
    Form1.frxReport.DataSets.Add(frxDBDataset3);

    //DESIGN MODE - comment the following two lines and uncomment the preview mode lines to preview report

    Form1.frxReport.LoadFromFile('.\Report\full_asset_report-backup.fr3');
    Form1.frxReport.DesignReport;


    //PREVIEW MODE - comment the following four lines and uncomment the above design mode lines to design report

    //frxDBDataset1.DataSet.Close;
    //frxDBDataset2.DataSet.Close;
    //Form1.frxReport.LoadFromFile(ExtractFilePath(Application.ExeName)+'Report\MyReport.fr3'); //this is my path and my report name, you need to use yours
    //Form1.frxReport.ShowReport;

    Results1.Free;
    frxDBDataSet1.Free;
    Results2.Free;
    frxDBDataset2.Free;
    Results3.Free;
    frxDBDataset3.Free;
    
    Form1.frxReport.Clear;
    Form1.frxReport.DataSets.Clear;
end;

NOTE : the customized report is called from Form2, but you will see calls to Form1 (the main application form). This is because the report handling is done by the main form of your application. Don't forget that

This example is built with 3 queries, but you can use as many as you want. The datasets produced will appear in design mode in your report under the names you'll have given them (here Asset, Reqs and Artists).

I can not send you this example because it is part of a very large project that is more than 200 Mo once compressed but if things are unclear, I'll arrange something to give you an example.

Hope this helped a little and was not too long to read.

have a good day

Cheers

Mathias

Hello VascoMorais,

Imagine you have 3 components on which you want to filter :

• a checkbox which can be checked or not

• a combobox which can have an element selected or not

• and edit field that can be empty or not

If you use the onChange event of those components to filter your query, ALL conditions must be specified in ALL onChange events of ALL components, with a specific SQL qury for each case.
So you end up with a lot of conditions in each onCange events (if checkbox is checked and edit field is empty or checkbox is not checked and combobox index > -1 and so on...). This is difficult to keep track of all the chained conditions and their effect. Not talking when you have more than 3 components for filtering.

But if you regroup all conditions in one procedure like I did, the logic is only in one place and you don't have to worry about AND and OR, just code what each component does and that's it. You only need one single filtering query and each element can be nullified with '1 = 1' (which is always true) or the filter issued by the component state.

Hope this makes things a little clearer.

Wish you a good week

Cheers

Mathias

Hello kkalgidim,

As I am the one usually finding complex solutions to simple problems, you might want to wait for others advises

I see two workarounds for your problem (not sure you can implement the second one in your MVD version though...).

The first solution (and the simplest) would be to (manually) create an index on your table containing the names. This usually speeds up searches quite a bit. I recommend you search for sqlite and indexes in google, you will see that this is pretty straightforward.

The second solution would be to implement a delay (timer) in your application. Like the user is typing letter in the search field but the search does not trigger before say 0,5 second. This way, the user has time to type in his search criteria without the query being triggered every time et types in a letter (which is the cause of your application slowing down).

First you have to declare a timer in you application. This is a global variable meaning you have to place it at the very beginning of your script, like this for example :

var
     TimerA : TTimer; //----->Timer for name search

Now that the timer is declared, you will need to create it. This is done on the OnShow event of the form where it will be used like this :

Formx_Onshow (Sender: string; Action: string);
begin
    TimerA := TTimer.Create(nil); // create timer
    TimerA.OnTimer := @OnTimerA;  // event procedure the timer points to
    TimerA.Interval := 500; // interval in milliseconds of your timer


....
end;

Do not declare it in the variable section of the form since you already declared it as a global variable

Now, what does the timer do ? Declare the procedure like this :

procedure OnTimerA (Sender: TObject);
var
    //any variable you need for your search query if any
begin
    // you code here with your own search logic
     TimerA.enabled := False;
end;

Don't forget to stop (enabled := false) your timer at the end of the logic, it is the typing of the user that will trigger it after each character type din.

Finally, in the OnChange event of your search field disable the timer and start it again to reset the interval in milliseconds you set up previously.

procedure edit1_OnChange(Sender: string);
     TimerA.Enabled := False; //stops the timer and reset the time interval
     TimerA.Enabled := True; //starts the timer again for the given time interval

Since MVD creates all forms at startup and not dynamically, do not forget to free the timer when the corresponding form is closed :

procedure FormX_OnClose (Sender: string; Action: string);
begin
     TimerA.Free;
end;

Now each time the user types in a character, the system will wait 0,5 seconds before firing the search query. each time the user type sin a new character, the timer is reset for another interval.

Hope this helps.

Cheers

Mathias

Hello hosam14,

It's easy :
- plug your bar-code scanner on your computer
- put an edit field on a form
- launch your application and click in the edit field (put your cursor in it)
- use your scanner to read the bar-code
- the code appears in the edit field

Easy right ?

Have a good day

Cheers

Mathias

Hello wenchester21,

Just one thing to add to what Dmitry answered.

You might have noticed that Dmitry used

for i := c downto 0 do

This is very important to process your StringList in reversed order like Dmitry said because if you happen to delete some strings (lines), the order of the stringlist changes and you might miss some of the strings. The reverse order processing of the list avoids this problem.

Have a good day

Math

Hello Jean,

No, the missing Button1 was a attempt at creating a search button instead of a SQL Query button, but the total duration of each album was not correctly calculated by the calculated field so I changed my mind.

The groups, albums and tracks data was filled... by hand directly into the database

Math

Hello Dmitry,

I had a look at your new beta version, and what you added is not a treeview... this is a DBTreeView and THIS IS AWESOME !!!

Very very nice my friend, I love it !

From what I see, it's better used with a recursive table containing ID's and parent ID's, right ?

I'll play with it and try to write a tutorial this week-end.

Nice job Dmitry,

Cheers

Mathias

PS : did not find the autoexpand property but I only looked fast

Hello Adam,

You can not (I think) through 2 SQLExecute at the same time in the same function. If you really want to query multiple tables at the same time in one query, the best option is to use the UNION keyword between your queries. But this will only work if the tables have the same fields, which is not the case. I think you'll have to consider working with stringlists.

Then, when you call (on import)

sFileName := 'ExportedData-' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv';

you refer to a file which name is composed of the date and time of your search, probably a few seconds after the export, so the systme can not find it because it does not exist. The export file was generated a few seconds earlier, thus the name is different. The problem here is the 'now' variable you are using.

A few lines down, you write :

CopyFile(ExtractFilePath(Application.ExeName)+'ExportedData.csv' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv', OpenDialog.FileName);
   ShowMessage('Data has been imported.');

Wrong name again, because you added '.csv' twice : once just after Application.Exename and another time at the end.

Come on my friend, you know better than that, I'm sure

Anyway, I'll try to through a couple of lines for you tonight.

Cheers

Mathias

Hello Adam,

Using StringList for exporting was only to get all data from X tables in a single List and exporting them to a single CSV file.

When importing, you need to differentiate the DB tables where the imported data will be saved, the StringList trick does not work anymore, at least not this way.

Furthermore, I see in your Form1_bImport_OnClick procedure a bunch of SELECT, while you are importing from a CSV file and saving to a database. Shouldn't those instructions be INSERT instead of SELECT ?

Importing is a whole different story. Using StringList would only bee useful if you want to compare to lists : the one to be imported, and the one built from database data to compare and find duplicates before saving.

Have a look at the IndexOf() function in TStrings : it lets you check for the presence of a certain string in another one. If indexOf =  - 1, the string was not found and then it's not a duplicate.

The general idea is this :
1 - build a stringlist with database data (this will give you all your fields separated by commas)
2 - build a second stringlist with data from the CSV file
3 - compare second list with first one to try and find sequence of this list in the original one
4 - if IndexOf = - 1 then you can save to DB because this is new data
5 - if IndexOf <> - 1 then this is duplicate data, discard it.

Try your hands on that, it's a very good exercise and you will need that often. if you encounter some problems, I'll try to find some time to build a simple example for you.

Furthermore, those lists have known counts, which could help you the progressbar you talked about in your first post.

Cheers

Mathias

Hello teco049,

You wraper is a good idea, but you can make it shorter with the use of ReplaceStr.
This command will do what you do but natively, that is to say checking each character in the string and replace it if matched.

To keep you double quotes or convert simple quotes to double, you could do :

procedure Form1_bt1_OnClick (Sender: string; var Cancel: boolean);
var
    text : String;
begin
    text := wraper(Form1.Edit1.text);

    SQLExecute('INSERT INTO customers(comment) VALUES ('''+text+''')');
    Form1.TableGrid1.dbUpdate;
    Form1.Edit1.Clear;
end;

//WRAPER FUNCTION
function wraper(input : string) : string;
begin
    Result := ReplaceStr(input,'''','"');
end;

Have a good week-end

Cheers

Math

Hello ehwagner,

If feel totally dumb not to have thought about this combination. I have tried a lot of them but not this one.

Thanks you my friend, works like a charm

Math