(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Wildcard search in a programmed retrieve?
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Hot Topic (More than 10 Replies) Wildcard search in a programmed retrieve? (Read 1297 times)
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Wildcard search in a programmed retrieve?
May 2nd, 2007 at 7:18pm
Print Post Print Post  
My Invoice form has six payment date/note/amount sets.  That is, we can record up to six payments on any invoice, with each posting containing a date, a note (for the check number or other) and an amount.

I've made a programmed retrieve that will find a record that contains today's date in any of the date posting fields:
Code
Select All
{ Payment_1_Date = @Date or Payment_2_Date = @Date or Payment_3_Date = @Date or Payment_4_Date = @Date or Payment_5_Date = @Date or Payment_6_Date = @Date }

This works well, and in fact I have saved variations which will retrieve previous days by just subtracting from @Date.

Now here's the wrinkle.  Sometimes the payment is deposited to the Savings account, in which case the Payment Note field (Payment_1_Note, etc) will have the words "Savings Deposit" entered.  Those need a special report run.  I would like to add an AND statement to the above which will also search any of the six Note fields, retrieving any record which contains the word "Saving"  (e.g. "..saving..").

This is giving me some trouble.  None of the examples in the book (that I've seen so far, though I'll keep looking) show a programmed retrieve that uses wildcards.  Is this even possible?  I've tried
Code
Select All
{as above} AND {Payment_1_Note = "..saving.."}

and
Code
Select All
{as above} AND {Payment_1_Note = ..saving..}

(I'm starting with the first Note field because if I can find a syntax that works I'll expand it later with ORs).

Anyway, I'll keep experimenting.  I'm gonna play with @INSTR next.
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Wildcard search in a programmed retrieve?
Reply #1 - May 2nd, 2007 at 7:55pm
Print Post Print Post  
Well, I'm breaking the problem down.  This seems to retrieve all the records that have "saving" in any of the six note fields:
Code
Select All
{ (@INSTR(Payment_1_Note, "saving") > 0)  or (@INSTR(Payment_2_Note, "saving") > 0)  or (@INSTR(Payment_3_Note, "saving") > 0) or (@INSTR(Payment_4_Note, "saving") > 0) or (@INSTR(Payment_5_Note, "saving") > 0) or (@INSTR(Payment_6_Note, "saving") > 0)}


Now I have to find a way to put the two together happily.
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: Wildcard search in a programmed retrieve?
Reply #2 - May 2nd, 2007 at 8:03pm
Print Post Print Post  
Since the field will have the words "Savings Deposit" in it, you could simply check for the entire string "Savings Deposit". If that string will vary, but will always have the term "Savings" in it, you can use @Instr. If you really need to use a search spec, you can use "@Rest".

For example:

{@Rest(Company, "..m..")}

in a search spec will match any companies that have a "m" in the name.
  
Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Wildcard search in a programmed retrieve?
Reply #3 - May 2nd, 2007 at 8:03pm
Print Post Print Post  
Looks like I've got it, for anybody interested:
Code
Select All
{Payment_1_Date = @Date or Payment_2_Date = @Date or Payment_3_Date = @Date or Payment_4_Date = @Date or Payment_5_Date = @Date or Payment_6_Date = @Date} AND { (@INSTR(Payment_1_Note, "saving") > 0)  or (@INSTR(Payment_2_Note, "saving") > 0)  or (@INSTR(Payment_3_Note, "saving") > 0) or (@INSTR(Payment_4_Note, "saving") > 0) or (@INSTR(Payment_5_Note, "saving") > 0) or (@INSTR(Payment_6_Note, "saving") > 0)}


This seems to get the records I want.  I'll need to test it more to be certain.

Funny thing, several times during my testing, the retrieved records all went gray, as if they were read-only, and, in fact, I couldn't make manual entries, although my command buttons still worked.  I had to exit the database and re-open it for normal retrieves.  Anyone familiar with this?
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Wildcard search in a programmed retrieve?
Reply #4 - May 2nd, 2007 at 8:05pm
Print Post Print Post  
Thanks Mark, @REST is new to me.  I'll have to read up and experiment with it.
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: Wildcard search in a programmed retrieve?
Reply #5 - May 2nd, 2007 at 8:11pm
Print Post Print Post  
Infinity wrote on May 2nd, 2007 at 8:05pm:
Thanks Mark, @REST is new to me.  I'll have to read up and experiment with it.


Its came in with Q&A compatibility.

BTW, I think you have too many curly braces and too few parenthesis in the code you posted. Try this instead:
Code
Select All
{ (Payment_1_Date = @Date or Payment_2_Date = @Date or Payment_3_Date = @Date or Payment_4_Date = @Date or Payment_5_Date = @Date or Payment_6_Date = @Date) AND ( (@INSTR(Payment_1_Note, "saving") > 0)  or (@INSTR(Payment_2_Note, "saving") > 0)  or (@INSTR(Payment_3_Note, "saving") > 0) or (@INSTR(Payment_4_Note, "saving") > 0) or (@INSTR(Payment_5_Note, "saving") > 0) or (@INSTR(Payment_6_Note, "saving") > 0)) }


Untested, but I think your second set of curly braces may be a problem.
  
Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Wildcard search in a programmed retrieve?
Reply #6 - May 2nd, 2007 at 8:17pm
Print Post Print Post  
Super!  Works great, and I can subtract days too!  Thanks Mark.

With @REST, if I'm reading the book correctly, I can eliminate the "> 0", is that right?
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Wildcard search in a programmed retrieve?
Reply #7 - May 2nd, 2007 at 8:45pm
Print Post Print Post  
Mark, your code is working well to retrieve Savings noted postings that occurred today (@Date) but I can't seem to get it to correctly retrieve yesterday (@Date -1).  Here's what I'm trying:
Code
Select All
{ (Payment_1_Date = @Date - 1 or Payment_2_Date = @Date - 1 or Payment_3_Date = @Date - 1 or Payment_4_Date = @Date - 1 or Payment_5_Date = @Date - 1 or Payment_6_Date = @Date - 1) AND ( (@INSTR(Payment_1_Note, "saving") > 0)  or (@INSTR(Payment_2_Note, "saving") > 0)  or (@INSTR(Payment_3_Note, "saving") > 0) or (@INSTR(Payment_4_Note, "saving") > 0) or (@INSTR(Payment_5_Note, "saving") > 0) or (@INSTR(Payment_6_Note, "saving") > 0)) }

This retrieves the one record that was posted yesterday to the savings account, but it also retrieves the 5 records posted today to the savings account.  I tried putting parens around each (@Date - 1) but that made no difference.

I hate date math.
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: Wildcard search in a programmed retrieve?
Reply #8 - May 2nd, 2007 at 9:02pm
Print Post Print Post  
Quote:
This retrieves the one record that was posted yesterday to the savings account, but it also retrieves the 5 records posted today to the savings account.  I tried putting parens around each (@Date - 1) but that made no difference.


I'd have to see the data to give you much advice. From the logic above, if any of the dates match and any notes contain "savings" then the record will match. Make sure your test cases are correct. If you are still not getting what you expect, then simply the case to as simple as you can make it, and add complexity in small increments.
  
Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Wildcard search in a programmed retrieve?
Reply #9 - May 3rd, 2007 at 4:20pm
Print Post Print Post  
Thank you, I see what I'm doing wrong.  My logic is faulty.  The case I'm looking for is to retrieve the record when both conditions occur for the field set.  That is, retrieve the record if Payment_1_Date is yesterday and Payment_1_Note contains "savings", or if Payment_2_Date is yesterday and Payment_2_Note contains "savings", and so on, matching each pair.  With the logic I'm using, a record will be retrieved if Payment_1_Date is yesterday and Payment_2_Note contains "savings", or any other mix of the two.

I'll have to refine this a bit further, combining AND's and OR's.  Thanks for your help.
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: Wildcard search in a programmed retrieve?
Reply #10 - May 3rd, 2007 at 4:43pm
Print Post Print Post  
Infinity wrote on May 3rd, 2007 at 4:20pm:
Thank you, I see what I'm doing wrong.  My logic is faulty.  The case I'm looking for is to retrieve the record when both conditions occur for the field set.  That is, retrieve the record if Payment_1_Date is yesterday and Payment_1_Note contains "savings", or if Payment_2_Date is yesterday and Payment_2_Note contains "savings", and so on, matching each pair.  With the logic I'm using, a record will be retrieved if Payment_1_Date is yesterday and Payment_2_Note contains "savings", or any other mix of the two.

I'll have to refine this a bit further, combining AND's and OR's.  Thanks for your help.


You will probably end up with something a bit like this:
Code
Select All
{ (((a1 = "x") and (b1 = "y")) or ((a2 = "x") and (b2 = "y")) or ((a3 = "x") and (b3 = "y"))) }


Note how the parenthesis control the precedence of the "and" and "or" operators.
  
Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
WWW WWW  
IP Logged
 
Infinity
Senior Member
Members
*****
Offline


Diagonally parked in a
parallel dimension

Posts: 1290
Location: Massachusetts
Joined: May 27th, 2005
Re: Wildcard search in a programmed retrieve?
Reply #11 - May 3rd, 2007 at 5:55pm
Print Post Print Post  
This is it.  This retrieves any Savings deposit posted yesterday:
Code
Select All
{ (Payment_1_Date = (@Date - 1) AND (@INSTR(Payment_1_Note, "saving") > 0) )
or  (Payment_2_Date = (@Date - 1) AND (@INSTR(Payment_2_Note, "saving") > 0) )
or  (Payment_3_Date = (@Date - 1) AND (@INSTR(Payment_3_Note, "saving") > 0) )
or  (Payment_4_Date = (@Date - 1) AND (@INSTR(Payment_4_Note, "saving") > 0) )
or  (Payment_5_Date = (@Date - 1) AND (@INSTR(Payment_5_Note, "saving") > 0) )
or  (Payment_6_Date = (@Date - 1) AND (@INSTR(Payment_6_Note, "saving") > 0) ) }

I prettied it up for display here.  Sesame doesn't like line breaks in retrieve programming, sad to say.  It's much easier to work with this way so I used a text editor, then killed all the breaks and planted it into an F6 box.

Thanks for all your help; I'm off and running!
  
**
Captain Infinity
Back to top
WWW WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge