NEED HELP

[NEW01]

hello all
I am new to vfp just learning
I have a tabel named tabel1
and have 4 field (A-B-C-D)
Field A and Field B should have numbers
I want to make a comparison of two records from The Active record
if they are equal then I want to type (True) in Record (C) if not I want to type
false in record (D)
for Ex:

what should I type for doing this?

 

[Olaf Doschke]

First of all the field type for c and d should be logical, that's boolean in VFP. And c and b should be nullable.
Then you want C to be .T. (VFPs true) if a=b and .NULL. (VFPs NULL) otherwise
And you want D to be .F. (VFPs false) if NOT a=b and .NULL. otherwise
It's a strange setup, but this could be achieved by

UPDATE Table1 SET C=IIF(A=B,.T.,.NULL.), D=IIF(NOT A=B,.F.,.NULL.)

The whole thing could become simpler with just one column C being logical and not nullable, you set that to a=b, which is either .T. or .F., then this reduces to

UPDATE Table1 SET C=(A=B)

C would only also need to be nullable if a or b are nullable fields, but the elegant thing here is you set C to the result of the comparison, so you can get the .T./.F./.NULL. result of a boolean expression and store it to a field or variable. In T-SQL (MS SQL Server) you could not set bit fields to a=b, you'd need to store CASE WHEN a=b THEN 1 ELSE 0 END or in late SQL Server versions could do IIF(a=b,1,0), but still not simply set c=(a=b).

Finally [tt]SET NULLDISPLAY TO ''[/tt], if you want to let fields show blank, when they're not true or false.

If C and D are char or varchar columns and you want to put in the words 'true' and 'false', then simply do so by modifying the update this way:

UPDATE Table1 SET C=IIF(A=B,'true',''), D=IIF(NOT A=B,'false','')

But I would rather suggest you only have one logical column C. In the end, you would not even need that, as you can always query (a=b) as c and having a permanent column only stores redundant information. As far as I see it, this should rather be an exercise to apply what you learned earlier and my help here may really just not tell you what your teacher or employer wanted to get as feedback from you.

Anyway, let's now finish by showing how a logical field will arrive in excel as TRUE and FALSE, if you simply export table1 to xls:

CREATE CURSOR Table1 (A I, B I, C L NULL DEFAULT .NULL.) && here the first NULL is for NULLable, therefore no dots there, the second is .NULL. as value, so with dots.
INSERT INTO Table1 (A,B) VALUES (12345,12345)
INSERT INTO Table1 (A,B) VALUES (22245,67854)
UPDATE Table1 SET C=(A=B)
EXPORT TO d:\sample.xls TYPE XL5

Excel will display TRUE and FALSE and in other language versions, the column c will display TRUE and FALSE in the language of Excel. The thing to notice is that VFPs .T. and .F. are translated to Excels boolean type, this is not done, when you have char fields. Also in VFP you wouldn't be able to continue "calculating" with char 'TRUE', just like you can't calculate with '12345', even though it is displayed exactly the same as 12345. So the final lesson to learn: Use the right field/data type for your values. Don't go for looks here, go for the best way to be able to process data in its purest form. If a browse window shows you something you're not used to, eg US date formatting, you shouldn't handle this by instead storing dates in char fields in the format you're used to, you'll likely not even be able to make date comparisons or sort by date, if it is in text form. You have settings in VFP to apply to dates, the easiest one is SET SYSFORMATS ON to get dates displayed as Windows locale is set up to, other things are then also changed to locale settings.

Bye, Olaf.

 

[GriffMG]

Assuming your table has a structure like this

CREATE TABLE 'TABLE1.DBF' NAME 'TABLE1' (A N(12,0 NULL, ;
B N(12,0) NOT NULL, ;
C L NOT NULL, ;
D L NOT NULL)

Then the last two fields can be populated thus:

REPLACE ALL C WITH IIF(A=B,.T.,.F.), D WITH IIF(A<>B,.F.,.T.)

But, you don't really need two logical columns in this instance, because the condition you are looking for is polar.
This means that D will always equal NOT A



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Welcome to the forum.

You've received two good answers to your question. I'll just add one small point. With Griff's solution, you can simplify the code slightly, as follows. Instead of this:
[tt]
REPLACE C WITH IIF(A=B,.T.,.F.)[/tt]

you can simply do this:

[tt]REPLACE C WITH A=B[/tt]

That's because[tt] A = B[/tt] is an expression, and it evaluates to either .T. or .F. So you are simply putting .T. or .F. in Field C.

Similarly with the other part:
[tt]
REPLACE C WITH A = B, D WITH A <> B[/tt]

But, as Griff pointed out, this is redundant. Field D gives you no information that you don't already have in Field C.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[NEW01]

Thanks a lot guys for the help