I have a field, Billing_Code, which can contain multiple text codes.  Based on the particular code the user enters, certain things need to happen.  Can this code be shortened?

IF Billing_Code="T" OR Billing_Code="Q" OR Billing_Code="A" OR Billing_Code="B" OR Billing_Code="C"
THEN {action} 

Infinity wrote on Jun 8^th, 2006 at 2:07pm:


Yes.

if(@instr("TQAB", Billing_Code) > 0)
{
  // Take Action
}
 

I'd like to adapt Mark's test statement:

if(@instr("TQAB", Billing_Code) > 0) 

to check a different field for any of 14 4-digit numbers (it's a text field holding numeric characters).  This is different from the easy "TQAB" structure.  Can this be done?  Would I use semicolons or some other separator?

Once I get this into Sesame I can use Erica's code, but right now I'm cleaning up Q&A, pre-translation.

Right now my code reads:

IF #4950="3500" OR #4950="6472" OR #4950="3444"
OR #4950="3481" OR #4950="3751" OR #4950="3915"
OR #4950="2897" OR #4950="3939" OR #4950="4029"
OR #4950="2318" OR #4950="3907" OR #4950="2075"
OR #4950="2834" OR #4950="4883"
	THEN
	{
	   // action
	} 

Thanks in advance.

Quote:

I'm not sure what you're asking.  The field being tested is a billing code field (customer number).  If any of those 14 codes are entered a message box pops up with special instructions for the user.

You're probably right about just leaving it as is in Q&A and then changing it in Sesame.

Quote:

Somewhat.  I have purchased John Dow's marvelous DTFDOCTR and DTFSPECS utilities and they have been very helpful in cleaning up my Q&A programming.  My program's sizes do have rather high percentages, but I haven't hit the limit yet.

My main problem is the programming structure.  Erika has taught me a lot about how a program should look in order to make sense of it, things that I did not know when I first wrote the Q&A programming.  The first time I tried an (experimental) translation of my Workorder and Invoice databases I was dismayed by the way the programming looked in Sesame.  You know how with Q&A you only have that little F6 box to work with - everything ran together.  It was totally baffling, especially since Sesame changed all my well-documented field numbers into field names.  John's utilities allowed me to extract all my programming into files I can edit with a text editor and then import back into Q&A.  Once that's done I will translate again, and when the field numbers change to field names I expect things will look a lot better in Sesame.  And then at that point I can begin shredding the heck out of it.

I still have yet to take the advanced Sesame programming classes, so in the meantime I'm reworking my Q&A programming, getting a refresher of how these databases work "under the hood", and making lots of notes for future tweaks.

This one particular piece of code is not very important.  I was just wondering it the @INSTR function could be used to test against multiple-character strings.

Thanks for your help.

Thanks for the advice Bob!  I've got most of that under my belt but a reminder never hurts.

Do you know if the @INSTR function will accept a separated series of characters?  Such as:

IF(@INSTR("13;427;956;386", Billing_Code) > 0) 

to check the Billing Code field for the values 13 or 427 or 956 or 386?

I suppose I could try an experiment, see if Q&A chokes on it.  Then I'll know. 

Quote:


Mark, could you please provide us with a code example of how it can be shortened? I ran into the same thing Infinity did.

Thanks.

I have Q&A on my laptop and this worked for me:

>#1:If @Instr("1234;5678;9000;4000;5000;", #1 + ";") > 0 Then #2 = "Yes" 

I have two fields. If I type one of the numbers in the list into #1, I get "Yes" in #2. If the number I type is not in the list, I don't. Note that the last value in the list has a trailing semicolon. The last value won't work otherwise.

I've found that this method needs quite a bit of tweaking to be truly reliable. That's why, in Sesame, I always use @FindStringArray instead of @InStr. Because it knows how to manage the values separately. Q&A doesn't have this function, so you need to have very consistent values to make the @InStr method dependable. Carl's addition of a begin marker is certainly a step in the right direction.  As long as none of your values have semicolons in them, his code should get it done for you.