Field to field comparison

[draxx]

Hi,

I think I'm getting stupid as I get older...lol. What I am trying to accomplish is to compare one address field in tbl3 to an address field in tbl. If they match then a ui message box would say not to proceed. The coding seems simple to me except....it dosesn't work...lol. My code is as follows:

dim tbl as P
dim tbl3 as P
dim user as C



tbl3=table.open("ameregl3.dbf")

user=ameregl3->address

tbl=table.open("nonserve addresses.dbf")

If user=nonserve_addresses->address THEN
ui_msg_box("Attention","This is a NON SERVICE address. Do NOT Proceed",48)
Else
End

tbl.fetch_find(address)

While .NOT. tbl.fetch_EOF()
tbl.Fetch_next()

END WHILE

END IF


tbl=tbl.close()

END

Seems simple in theory but obviously I do not have enough experience in A5 yet. The above code works fine but unfortunately it doesn't seem to compare the fields in "tbl" to "tbl3" and display the message box if there is a matching address. I believe what it is doing now is checking the 1st field in "tbl" and then just fetching the other records in "tbl" without compaing them with the "address" field in tbl3. This is performed with a button on a form that is for tbl3, so in actuality (sic), tbl3 is already open.

Open to suggestions... Thanks in advance for all the help.... this includes the past. This message board has to have the greatest people out there involved.

Thanks again,

Scott

P.S. Probably won't be able to check the board again until Thursday Morning... Thx

[MikeC]

Re: Field to field comparison

Scott,
There are a few problems in your script and in the method.....and in your description of what exactly you are trying to do. :)

First you are not even using the table pointers as you should be for the most part.
Second, your loop through the table is after when you are trying to get values.

The following will take the first record of tbl3 and compare it to each record in tbl unless a match is found--then the script ends after the message box. If speed is an issue (a lot of records), then a tbl.batch_begin() and tbl.batch_end() should be placed outside of the While...End While.

This is most likely NOT exactly what you are trying to do though--see below for questions!

dim tbl as P
dim tbl3 as P
dim user as C

tbl=table.open("nonserve addresses.dbf")
tbl3=table.open("ameregl3.dbf")

tbl.fetch_first()
tbl3.fetch_first()
While .NOT. tbl.fetch_EOF()
   user=tbl3.address

   If user=tbl.address
           ui_msg_box("Attention","This is a NON SERVICE address. Do NOT Proceed",48)
               End
        tbl=tbl.close()
        tbl3.close()
   end if

   tbl.Fetch_next()
    
END WHILE
    
tbl=tbl.close()
tbl3.close()
END

Now if you want to compare ALL the records in tbl3 to all the records in tbl then another loop is to be placed outside of the above for the tbl records.

If instead you have a specific value you want to compare to all the records in tbl I need to know how this value is derived....if from a form then no reason to open the tbl3 table at all, but just check this value in the above loop....same thing for if the value comes from a variable.

[Tom Henkel]

Re: Field to field comparison

Scott,

Your code does 1 compare, and if there is no match, it ends.

If user=nonserve_addresses->address THEN
ui_msg_box("Attention","This is a NON SERVICE address. Do NOT Proceed",48)
Else
[COLOR="Red"]End[/COLOR]

Mike's code will do the compare for all the records.

This whole thing seems a little cryptic, though.

If you do get a hit, you display a message and quit. Does that mean that if only one address matches, you want to quit?

Again, I don't know all your logic, but if you are trying to make sure non-serviced addresses are bypasses in some other routine, like a report or query, you may want to "mark" those records as yo work through the tables, then use a ".not.marked()" as a filter on whatever other routine you are using to process the "good" records.

Just my observations,

Tom

[draxx]

Re: Field to field comparison

Hi Guys,

Thanks for the replies. Basically, what happens is this.... A person is checking just one address on one record in tbl3 and comparing it to all the records in tbl. However, in tbl, there will be less than 50 entries. So again, we are trying to compare just one address to all the addresses listed in tbl. If there is a match, then the dialoge box pops up and my employee will know to close out that particular record.

Hope that makes a little more sense.

Thanks again for all the help.

Scott

[draxx]

Re: Field to field comparison

So sorry, I forgot to answer your question MIke. The value is derived from a form linked to tbl3.

Hope this helps.

Thanks again,

Scott

[Mike Wilson]

Re: Field to field comparison

Why loop anything. It seems the user is in the form of tbl3. Why not get the address of this record and query tbl for the presence of this address in the tbl. Also, 50 records is nothing. If this needs to be done over and over, get 50 addresses into a variable with one opening of the table using table.external_record_content_get() one time and query that variable for the presence of the address. This is where I have learned to use filter_string_smatch(). Very powerful and very fast.

tbl3 as p
dim vadd as C
tbl3=table.get("ameregl3")
  vadd=tbl3.address
  if a5_get_records_in_query("nonserve addresses","address="+quote(vaddr))>0
     ui_msg_box("Attention","This is a NON SERVICE address. Do NOT Proceed",48)
  end if
  End

[draxx]

Re: Field to field comparison

Hi Guys,

I believe Mike's script will work if I take out the command to fetch each record in tbl3. However I do need to make sure that the focus is on the current record and address field of tbl3. The button to run this script is on the form where the address field appears in tbl3. I would think that it should put the focus on that particular record and field that is referenced. Not sure about that part though.

Thanks for all the help!!

Scott

[draxx]

Re: Field to field comparison

I will give that a try Mike. It must be great to be so knowledgeable of A5 scripting. Slowly but surely I'm working on it...lol.

Thanks again,

Scott

[draxx]

Re: Field to field comparison

Hi Mike,

I tried your code but it did nothing unfortunately. I made sure to correct the first line and add "dim as". I'm still wondering if the focus is not on the current record in tbl3 that is currently open. Not sure.

As for looping, that was the only method that I am familiar with to accomplish this task.... I am still working at it though....lol.

[MikeC]

Re: Field to field comparison

If all you are doing is checking to see if a record exists, then I would use something along the following.

I am still not entirely certain of how you are getting the value of the address you wish to check.
if "The value is derived from a form linked to tbl3." means that you are on a record in a form that is to be checked, then you can get that record's ID value (your unique value that I am assuming you have created for each record--or have had Alpha do it...). I would take that value....or set a variable to that value first....and just use the Exist() function....but this means creating an index for the address field if it has not already been done.
http://support.alphasoftware.com/alphafivehelpv10/Functions/EXIST().htm

dim vaddress as c
vaddress=topparent:your_field_name.value

If Exist(vaddress, "ameregl3", your_address_field_index)
    msgbox("Field already exists!")
  else
    'do whatever here if anything when it doesn't exist
end if

I am not intentionally trying to confuse you, but just giving what I think is the best choice of many that can be used for your specific case.

[draxx]

Re: Field to field comparison

Thanks Mike I'll try that. Basically I AM looking to compare the value in one field to the value in another field in another table. If the value exists in both tables, then the script ends and my employeeenters some info and closes the current record they are working in which is where the first value is derived.

Not sure if that's clear but in a nut shell, that's what I am trying to accomplish.

Many Thanks!!!

Scott

[draxx]

Re: Field to field comparison

Hi Mike,

I tried your code and still nothing. Here is the code:

dim vaddress as c

vaddress=topparent:address.value

If Exist(vaddress, "nonserve addresses", "address")
ui_msg_box("ATTENTION!""Field already exists!",48)
else
END
end if

END

I assume this will check the value in the "address" field in amegl3.dbf (which is what the topparent form is attached to), and then compare that value to all the entries in the "address field" of nonserve addresses.dbf. If this theory is correct, when I run the script (from a button on the currently opened form), nothing happens. I have the address in the current form also entered into the nonserve addresses table. Theoretically, it should see that and the message box should pop up.

Not sure where I'm still going wrong. I won't be back in my office until Monday morning. Thanks for all your help and direction Mike. Maybe you can create 2 tables and a form and try it yourself. Anyway...

Many Thanks,

Scott

[MikeC]

Re: Field to field comparison

EDITED!!!

add a comma to the ui_msg_box(). The second argument is the table name---You should not have any spaces in table/field/index names as dictated by Alpha's naming convention. I am assuming that the 3rd argument is your index "address". When entering code into the code editor, right click between the arguments and many times a drop down will appear for choices--the index argument has this feature.

If Exist(vaddress, "nonserve addresses", "address")
    ui_msg_box("ATTENTION!"[SIZE=6][COLOR=Red],[/COLOR][/SIZE]"Field already exists!",48)
  else
    END
end if

Eliminate the last END

[Mike Wilson]

Re: Field to field comparison

Mike,
I love you bro, but let's get on board so we don't spin this guy into a tizzy. Why complicate things with a damn index. Screw the index, it only makes things unneedingly complicated. There are only 50 records in the nonserve addresses lookup table. An index is unnecessary. The code I gave is the simplest without need to manage an index. I just didn't DIM the pointer. It should work just fine.

[COLOR="Red"]DIM[/COLOR] tbl3 as p
dim vadd as C
tbl3=table.get("ameregl3")     ' current record in focus on the form
  vadd=tbl3.address
  if a5_get_records_in_query("nonserve addresses","address="+quote(vaddr))>0
     ui_msg_box("Attention","This is a NON SERVICE address. Do NOT Proceed",48)
  end if
  End

[MikeC]

Re: Field to field comparison

so we don't spin this guy into a tizzy

My worry exactly....just so many ways to do as Scott wants....and we don't know how familiar he is with programming in general.

Why complicate things with a damn index. Screw the index, it only makes things unneedingly complicated

:D :D

I believe that indices only appear complicated when you don't know or understand what they do or how to create one.
But have to say that learning about indices early on would have helped me considerably as it opens up many more functions that require an index. Even just knowing in a very general way that all an index really is, is a way to order the records of a field(s) in a table--analogous to the index in a book....and that ordering the records in this way for a specific field you are "querying", will speed up the process sometimes significantly. As so many functions require an index, I really do think it is prudent, even for xbasic newbies, to learn about them.

So to create an index, you right click on a table----choose Define Indexes----create a name (10 characters or under generally), choose the relevant field, the order (ascending usually), and type (usually All). Usually no filter at all except for very specific situations (have read that filtered indices can corrupt more easily--not proven IMO).

There!! Definitely enough said!! :)

Scott, just choose any of the methods/choices given and we should be able help get it going for you--but for sure try to understand exactly what is being given.