IIF( ) Function in grid column if condition applies

[keepingbusy]

Hi

I have a table (MYTABLE) with multiple columns one of which I want to show a certain field if a condition applies or another field if it doesn't.

A field within the table (MYFIELD1) is a numeric field which either contains the number 1 or higher.

Two other fields (MYFIELD2) and (MYFIELD3) are character fields which contain text.

If the record in that table has a number one I want MYFIELD2 to show in let's say column3 of the grid and if the record (MYFIELD1) > 1 I want MYFIELD3 to show in column3 instead of MYFIELD2.

Firstly, is that possible?
Secondly, how do I implement an IIF statement in the controlsource of the grid's column (or is there some other way)?

I am using VFP9

Thank you

Lee

 

[Mike Lewis]

Lee,

Yes, it's possible. And, of course, there are various ways of going about it.

My preference would be to create an intermediate cursor to populate the grid. The cursor would contain exactly the values that you want to show in the grid, in the same columns. In other words, you would do the conditional stuff at the point of creating the cursor, not in the grid.

Something like this:

SELECT Field1, IIF(Field1 = 1, Field2, Field3) AS AnotherField, .... ;
  INTO CURSOR csrGrid

Then use csrGrid as the RecordSource for the grid.

A completely different approach would be to use the column's DynamicCurrentControl property, but I suggest you at least consider the above solution first.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[keepingbusy]

Hi Mike

Thank you for the post. Yes, I understand the concept of your suggestion. The actual form where the grid is diplayed is a one-too-many form and the grid is populated with child records linked by a field from both tables.

Will this make a difference to what you have suggested and is it not possible to use the actual table and not a cursor?

Regards

Lee

 

[Mike Lewis]

Lee,

I can't see that it will make any difference that the grid shows child records. You would generate the cursor each time you move to a different record in the main table. The SELECT statement would have a WHERE clause to select only those child records that match the current parent.

However, if you prefer a different approach, you could consider using DynamicCurrentControl instead.

In summary:

1. Add a second textbox to the grid column that contains Field2 / Field3.

2. In that coloumn, set the ControlSource of one of the textboxes to Field2, and the other to Field3.

3. In the grid's Init, set the column's DynamicCurrentControl property to "IIF(Field1 = 1, 'Text1', 'Text2')", where Text1 and Text2 are the names of the two textboxes. Be sure to put quotes around the entire expression.

If you're not familiar with the DynamicXXX properties, here is a useful guide:
Conditional formatting in a Visual FoxPro grid.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

You already gave the answer yourself, IIF(). You can't think of one place to put that?

Mike has shown one, two other ways to apply that: As you have tables opened related as one-to-many already, it's uncomfortabel to change to a query. But you can simply let the controlsource of the grid's Column3 be (IIF(myField1=1,myField2,myField3)). That renders the column3 read only, though.

The other solution is to make use of dynamiccurrentcontrol, add two textboxes to the column, one bound to myField2, one bound to myField3 and switch between the two textboxes via .Column3.DynamiccurrentControl = "IIF(myField1=1,'Textbox2','Textbox3')".

You know how to add controls to a grid column? If not, you simply drag on to the header in the designer, or you use Column3.AddObject('Textbox2','Textbox') and Column3.AddObject('Textbox3','Textbox') in it's Init(). Don't forget to set their visible property to .T., if you add them programmatically. Even if they also are drawn without that (a bug of the grid), only setting them visible makes them editable.

Bye, Olaf.

 

[JRB-Bldr]

he actual form where the grid is diplayed is a one-too-many form and the grid is populated with child records linked by a field from both tables.

It won't make a bit of difference.

Instead of the One-to-Many relation Parent table being your 'real' table, you would now use, as the relation Parent, the Cursor that results from the query that Mike has shown you.

And if the user made changes to the values seen in the Form's grid they would be making it to the Display Cursor, but you could easily 'mirror' those changes back to the 'real' table.

The only thing I might add to Mike's recommendation above would be to change
INTO CURSOR csrGrid
to
INTO CURSOR csrGrid READWRITE

Good Luck,
JRB-Bldr