Putting an expression into a grid column, instead of a field value 1

[Doug Lindauer]

I'm not having much luck trying to put a UDF expression into a grid column. I'm using VFP 9 on Win 7.
What I have is an application for a camp. The camp has multiple sessions during the summer. The session table is the one I'm displaying on the grid.
Each session can utilize camp cabins and those cabins are in a separate related table via a session ID. The various cabins have a max number of beds in them.

So in my Sessions grid I want to include a calculated column to show the max number of beds. A UDF sums up the beds in all the cabins that are associated with each session. So the calculated value in the text box control source is something like this: MAXBEDS(SESSIONID).

MAXBEDS() returns a number.

When I run the form, the grid goes a little wonky and instead of showing the number it shows the value of the first column in the grid! I can't seem to find any instructions about how to put an expression into a grid. I would have thought it would be straight forward. Silly me.

 

[Olaf Doschke]

1. A grid always has to have a cursor or table as recordsource, even if you don'tshow any of its fields as controlsource.
2. Due to a bug expressions in controlsources need to be enclosed in bracets, so try to set it to (MAXBEDS(SESSIONID))
3. If your function MAXBEDS moves around in the table you have bound to the grid,all kinds of problems can occur based on that.
4. If your grid nevertheless just shows the fields, you have a grid reconstruction somewhere,the grid recoursource has to be available soon enough, or the grid takes anything else as recordsource and overrides whatever you set at designtie.

Bye, Olaf.

 

[GriffMG]

If your function moves the current record pointer or order, you should make sure it returns to where it was.

Example of well behaved 'look-up' code for use in a grid, makes note of record pointer and order and restores on exit

FUNCTION GETAPPROVEBYREF
	PARAMETERS m.DESIGNATOR,m.CODE,m.DEFASETTING
	PRIVATE m.DESIGNATOR, m.CODE, m.DEFASETTING, m.OLDAREA, m.OLDRECNO, m.OLDORD, m.FLAG
	IF PCOUNT() < 3
		m.DEFASETTING = .F.
	ENDIF
	m.OLDAREA = SELECT(0)
	SELECT MyTable
	m.OLDRECNO = RECNO()
	m.OLDORD	= ORDER()
	SET ORDER TO MyOrder
	** Designator+Code
	SEEK (m.DESIGNATOR+m.CODE)
	IF FOUND()
		m.FLAG = MyTable.APPROVE
	ELSE
		m.FLAG = m.DEFASETTING
	ENDIF
	SET ORDER TO (m.OLDORD)
	IF m.OLDRECNO > 0 .AND. m.OLDRECNO <= RECCOUNT()
		GOTO m.OLDRECNO
	ENDIF
	SELECT (m.OLDAREA)
	RETURN(m.FLAG)

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]

Another possibility:

Open the two tables in new work areas (using USE ... IN 0 AGAIN). Then use those new work areas in your MAXBEDS() function.

Then create a cursor, something like this:

[tt]SELECT *, MAXBEDS(SessionID) AS MaxBeds FROM Sessions INTO CURSOR csrGrid[/tt]

You would probably do that in th Init of the grid. Then, also in the Init, set the grid's RecordSource to "csrGrid".

The point is that, if you call a UDF from within a SELECT, the UDF must not move the record pointers in the tables that are referenced by the SELECT. By using separate work areas within the UDF, you avoid that problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Doug Lindauer]

Thanks for all the answers. The use of parentheses was the first problem. I also found that I had to use the expression in the control source for the column and not the control source for the text box. The work area wasn't a problem because I used sql selects and then reselected the work area on exit. I've included the actual function below in case anyone was interested. Unfortunately this thing trims white space so the proper indentation is gone.

I've got it working now (after I don't know how many hours of trial and error!)

*-------------------------------------------------------------------------------
function BEDCOUNT       && Total up the number of beds available for a camp for a particular type of cabin: Boy, Girl, Family, Team
*-------------------------------------------------------------------------------
    parameters pEVENTID, pGENDER
    local mBEDCOUNT, mLASTSELECT

    mLASTSELECT = select()
    if upper(pGENDER) = 'TOTAL' .or. empty(pGENDER) && If you don't specify anything, sum the total
        select sum(BEDS) as MAXBEDS from EVENTCABINS where EVENTID = pEVENTID into cursor curBEDCOUNT

      else                                          && .not.empty(pGENDER)
        select sum(BEDS) as MAXBEDS             ;
          from EVENTCABINS ec                   ;
         where EVENTID = pEVENTID               ;
         .and. upper(ec.TYPE) = upper(pGENDER)  ;
         .and..not.empty(ec.TYPE)               ;
         into cursor curBEDCOUNT        
    endif

    mBEDCOUNT = iif( isnull(MAXBEDS), 0, MAXBEDS)
    use
    select (mLASTSELECT)

    return mBEDCOUNT

 

[Olaf Doschke]

>this thing trims white space so the proper indentation is gone.
You return a numeric value here, numeric values are by default displayed right aligned.
In a font, that isn't monospaced you'll always have misalignments alone because 1 is slimmer than 0, so simply set the font to courier, and you have good alignment,

Bye, Olaf.

 

[Doug Lindauer]

Actually I was talking about this forum thing, not my program. Sorry if I wasn't clear about that. I can see now that one of the buttons is for posting code. I've used that to fix my post and I'll remember to use it in the future. But thanks for your help Olaf, it got me pointed in the right direction.

 

[Olaf Doschke]

>I was talking about this forum thing
Ah, I see.

>I also found that I had to use the expression in the control source for the column and not the control source for the text box
The help topic about the "Bound" property of the column explains that:

https://msdn.microsoft.com/en-us/library/ye3s3478(v=vs.80).aspx

Notice even setting this to .F., the columns ControlSource overrides the grids Textbox.Controlsource, when it's set:

If a column's Bound property is set to false (.F.), you can set the ControlSource property of a contained control directly. If you subsequently set the ControlSource setting of the column, it overrides the ControlSource setting of the contained control.

You'll rarely ever make use of that anyway, as the Textbox also is a single textbox for all rows, it's just drawn multiple times, and so there's only one controlsource anyway and you set the columns controlsource. If you replace a grid textbox with a container with even a set of controls inside it (which would defy the usage of the grid a bit, but would make use of it repeatedly drawing rows per record of the grid recordsource) you'd have normal controlsource behavior in that regard, but that's not encouraging that as a solution. It can be one for grids having a 2 row per record or even more complex layout you can't do in the mere grid layout.

Within a grid you should be able to control it mainly by using the grid and grid column properties, to me that's design by the POLS principle and maybe most people actually feel the same, but don't come to forums, then, as the grid works for them. I can't tell how much help you need with the grid, so instead of trying for hours, you can get such hints asking. It might take hours to get the answer anyway, but hours you can spend for other things.

Bye, Olaf.

 

[Mike Lewis]

Unfortunately this thing trims white space so the proper indentation is gone.

It looks like you've already found the solution to that. Use the [ignore]

/

[/ignore] tags. You can either type them manually or use the relevant button on the toolbar (which is just above the message composing area).

If you only have a single line of code to dispaly, or you want to display mono-spaced text within a line of ordinary text, you can use the [ignore][tt]/[/tt][/ignore] tags instead.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Doug Lindauer]

Thanks to all. Great to know I'm not alone out here. I've found VFP to be a quantum leap moving from the dBase/Clipper world so having a brain trust to tap is invaluable.

 

[Olaf Doschke]

So from which version are you moving, or are you rather picking up VFP instead of going back to dBase/Clipper?

MS deprecated VFP, so if you're interested in something along the lines of XBase with future, the best bet is on Alaska Software Xbase++.

Bye, Olaf.