Recent

Author Topic: Lookup question  (Read 1255 times)

daveinhull

  • Sr. Member
  • ****
  • Posts: 264
  • 1 divided by nothing must still be 1!
Lookup question
« on: February 27, 2019, 02:31:04 pm »
Hi,

I'm trying to use the
Code: Pascal  [Select]
  1.  function Lookup(const KeyFields: string; const KeyValues: Variant; const ResultFields: string): Variant;
function with 2 fields. According to the help file:
Quote
KeyFields can be a single field name or a semicolon-separated list of fields.

KeyValue can be a variant or a variant array and the number of items must match the number of fields specified in KeyFields.

My KeyField is already 2 DB fields separated by a ';', but I just can't work out how to pass a variant array of two values for the KeyValue parameter (in ?

I tried
Code: Pascal  [Select]
  1.   with TRxDBLookupCombo(Sender) do
  2.    if ((Text <> '') and (LookupSource.DataSet.lookup(LookupDisplay,[Text,Text],'ID') = Null)) then

Could someone point me in the right direction.

Thanks
« Last Edit: February 27, 2019, 02:37:26 pm by daveinhull »
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Sr. Member
  • ****
  • Posts: 264
  • 1 divided by nothing must still be 1!
Re: Lookup question
« Reply #1 on: February 27, 2019, 02:37:19 pm »
HI,

Ok, I found it in the help file for the Locate funciton, all sorted.

Thanks
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 6235
Re: Lookup question
« Reply #2 on: February 27, 2019, 02:57:17 pm »
In "Locate" you have the same problem: How to assign an array to a variant? Use VarArrayOf from unit variants. Assuming you have a DB with fields "Name" and "FirstName" and you want to find a record of a person named John Smith you would call:
Code: Pascal  [Select]
  1.   Dataset.Locate('Name;FirstName', VarArrayOf(['Smith', 'John']), [loCaseInsensitive]);
« Last Edit: March 04, 2019, 10:36:23 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

madref

  • Hero Member
  • *****
  • Posts: 688
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Lookup question
« Reply #3 on: March 04, 2019, 09:31:54 pm »
I wrote this function once for databases
Code: Pascal  [Select]
  1. unction MLookup(Veld, Tabel, Voorwaarde: string): variant;
  2. var mySql: TSQLQuery;
  3. begin
  4.   mySQl := TSQLQuery.Create(nil);
  5.   mySQl.DataBase := Form_RefereeMain.Connect_RefereeDB;
  6.   try
  7.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
  8.     mySql.Open;
  9. //    Result := mySQL.Fields[0].AsVariant;
  10.     Result := MySQL.Fields[0].Value;
  11.   finally
  12.     MySql.Free;
  13.   end;
  14. end;     // MLookup
  15.  
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

Soner

  • Full Member
  • ***
  • Posts: 159
Re: Lookup question
« Reply #4 on: March 04, 2019, 10:03:23 pm »
I wrote this function once for databases
Code: Pascal  [Select]
  1. unction MLookup(Veld, Tabel, Voorwaarde: string): variant;
  2. var mySql: TSQLQuery;
  3. begin
  4.   mySQl := TSQLQuery.Create(nil);
  5.   mySQl.DataBase := Form_RefereeMain.Connect_RefereeDB;
  6.   try
  7.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
  8.     mySql.Open;
  9. //    Result := mySQL.Fields[0].AsVariant;
  10.     Result := MySQL.Fields[0].Value;
  11.   finally
  12.     MySql.Free;
  13.   end;
  14. end;     // MLookup
  15.  

This is not lookup.
Lookup searchs in existing dataset(=resultset), you are sending new query to database.

madref

  • Hero Member
  • *****
  • Posts: 688
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Lookup question
« Reply #5 on: March 05, 2019, 01:04:07 pm »
This is the equivalent from the DLookUp in MS Access
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave