How to create an appointment calendar or populate grid question 1

[JeffAlisonVFP9]

I am a Visual FoxPro Version 9 user and I'm trying to create an appointment management system for our business. I have been using FoxPro for a while so I apologise if this question is easy for some (or if the answer is staring me in the face).

When you create a grid with fields from a table (dbf) it is populated with records from that table (e.g. A column showing a SURNAME, column showing a FORENAME etc but these are from the records in the table.

What I want to do is have the fields from a single record (e.g. FORENAME, SURNAME, APPTIME, APPTYPE etc) in that table vertically in a grid so its shows (as an example):

08:00 SURNAME FORENAME APPTYPE

08:15 SURNAME FORENAME APPTYPE

08:30 SURNAME FORENAME APPTYPE

and so on...

I suppose I am looking to do something similar to an Outlook Diary but my goal is to create this application so we can add, update, edit appointments. I have already set up a form with command buttons that allow a search for date, next record, previous record etc. I will also add a command to the grid (which I know how to do) so I can scroll down the list of the appointments in the grid then click on the command button so I can update the necessary appointments.

I would appreciate your guidance and comments.

 

[Mike Lewis]

In fact, this is fairly simple.

In general, if you want each row of the grid to contain data from one field in a record, you should create a cursor. (A cursor is just a temporary table; see the CREATE CURSOR command in the Help for details).

The cursor would have a single field. Each record in the cursor contains the data from the relevant field in the original table. So, in this example, you would have one record for 08.00, one for 08.15, and so on.

It's then an easy matter to use the cursor as a RecordSource for the grid, in place of the original table.

However, I have to say that it would make more sense to design your original table so that each time slot is a different record. This might involve having two tables, one for the stuff that's the same for all time slots, and one for the actual time slots.

It's hard to give more details without knowing about the structure of your table, but what I am describing is generally considered a better design.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Just to add ...

If you really want to create a good-looking Outlook-style calendar, you should look at the ActiveX controls sold by DBI (

http://www.dbi-tech.com).

They have some excellent calendar contols that work well in VFP.

Unfortunately, their products are quite expensive, but if you are developing a professional application, it might be worth the price.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JeffAlisonVFP9]

Hello MikeLewis

The cursor would have a single field. Each record in the cursor contains the data from the relevant field in the original table. So, in this example, you would have one record for 08.00, one for 08.15, and so on.

Ok, sounds like what I could be looking for. I'll check the help file.

Just one thing though, would that display the fields vertically in the grid as my original post?

If you really want to create a good-looking Outlook-style calendar, you should look at the ActiveX controls sold by DBI (

http://www.dbi-tech.com).

They have some excellent calendar contols that work well in VFP.

I will have a look at this (thank you)

 

[JRB-Bldr]

Just one thing though, would that display the fields vertically in the grid as my original post

It is up to you how you create the Cursor (or temporary table) and how you populate it.

If you made the cursor only contain a single field and then created records for each field in your original table and populated each record's field with the field values from a single record in your original table, you would end up with what you describe. Or some variation of that.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

would that display the fields vertically in the grid as my original post?

Each "field", that is, each time slot, is a separate record in the cursor. So the grid is just doing what it normally does, that is displaying one record in each row. You don't have to do anything special to make it happen.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

I see that Codejock (

http://www.codejock.com)

also have an Outlook-style calendar control.

Codejock is likely to cost a bit less than DBI, and their controls work OK in VFP. I use their grid control, and have had good results with it. But the documentation is appalling, and the support is at best indifferent.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JeffAlisonVFP9]

MikeLewis:

The help file gives an example for create cursor as:

CREATE CURSOR employee ;
 (EmpID N(5), Name Character(20), Address C(30), City C(30), ;....etc

Is the same principle as:

SELECT * FROM MYDBF WHERE FIELDNAME=condition INTO CURSOR mytable

I still dont have an understanding of how you get the fields from one record to show in a grid (Sorry).

jrbbldr

If you made the cursor only contain a single field and then created records for each field in your original table and populated each record's field with the field values from a single record in your original table, you would end up with what you describe. Or some variation of that.

I'm sorry I didn't understand that either.

I've not looked at the software suggested (by MikeLewis) as yet.

 

[Mike Lewis]

Suppose your original table has fields like this:

Slot1 C(32)
Slot2 C(32)
Slot3 C(32)
etc.

These fields are all in the same record, and let's say the record has an ID field which is set to 1.

What I had in mind was something this:

CREATE CURSOR csrSlots (TimeSlot C(32))
SELECT MyTable   && this is the original table
LOCATE FOR ID = 1
INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot1)
INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot2)
INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot3)
   etc. etc.

You can then use csrSlots as the RecordSource for the grid.

I'm not saying the above code is the ideal way of doing it. It could be tedious if you have many timeslots. But it will give you the general idea, so that you can get a rough idea how it looks.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

swalesfoxuser,

I already saw your question in the MSDN Foxpro General Forum and didn't answer, as what you show and describe contradicts each other, so I kept myself off of your question.

But let me ask:

You give a visual exmpla of what you want as:

08:00 SURNAME FORENAME APPTYPE
08:15 SURNAME FORENAME APPTYPE
08:30 SURNAME FORENAME APPTYPE

But you say you want "the fields from a single record ... vertically"

What you show visually is having the fields horinzontally, that is a column for each table/cursor column and that's exactly what the grid does.

So what is your problem?

Do you rather want to display

08:00      08:15 
SURNAME1   SURNAME2 
FORENAME1  FORENAME2
APPTYPE1   APPTYPE2

You better use something else than a grid then. A grid by definition only shows the same type in each column. It needs trickery or cumbersome dta transformation of records to columns to display something different.

Bye, Olaf.

 

[JRB-Bldr]

While I am not 100% clear on what you are after here is one way to build your cursor of field names:

* --- Create Cursor ---
CREATE CURSOR ThisRecord;
    (FieldName C(10),;
     FieldValue C(10))

* --- Get Field Names From Orig Table ---
SELECT MyTable
DIMENSION aryFlds(1)
nFldCnt = AFIELDS(aryFlds)

SELECT MyTable
GO nDesiredRec
FOR FldCntr = 1 To nFldCnt
   cFldName = aryFlds(FldCntr,1)
   cFldType = aryFlds(FldCntr,2)
   IF cFldType = "C"
      * --- If Field Type Is Char, Populate Cursor ---
      cFldValue = EVAL(cFldName)

      SELECT ThisRecord
      APPEND BLANK
      REPLACE FieldName WITH cFldName,;
          FleldVal WITH cFldValue
   ENDIF
ENDFOR

* --- Cursor Now Has Field Names 'Vertically' and associated Field Values ---
SELECT ThisRecord
* --- Debug - Examine Results ---
BROWSE

Good Luck,
JRB-Bldr

 

[JeffAlisonVFP9]

Ok Mike, with your suggestion this is what I have done:

Created a "test" table with the appropriate fields and added some times (08:00, 08:15 in 15 minutes increments to 09:00), five records in all

I used the following code:

CREATE CURSOR csrSlots (TimeSlot C(32))
SELECT MyTable   && this is the original table

GO 1

INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot1)
INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot2)
INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot3)
INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot4)
INSERT INTO csrSlots (TimeSlot) VALUES (MyTable.Slot5)

DO FORM testform

I added the cursor name to the RecordSource on the Data tab for the Grid
The RecordSourceType is set as 1 - Alias

Now the result I get is very promising with only one minor issue.

When the form displays it shows the last record:

When you scroll the form you get:

Could you please explain how I can get the grid on the form to show the first field at the top (e.g. Slot1)

Thank you

 

[Olaf Doschke]

You simply need GO TOP and grid will display the table/alias from top.

Bye, Olaf.

 

[JeffAlisonVFP9]

Olaf

You simply need GO TOP and grid will display the table/alias from top.

I tried that in the forms INIT by adding:

GO top
thisform.Refresh

I still get the same result.

With regards to posting this question in another forum, yes that it is right. But having done some research trying to find a sollution, it appears to me that the best place to get answers is here (Unless you know otherwise)

Thank you.

 

[Mike Lewis]

When you do the GO TOP, are you sure that csrSlots is in the current work area? It would be better to do:

GO TOP IN csrSlots

Try that first, and if it still doesn't work, we can think again.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JeffAlisonVFP9]

MikeLewis

GO TOP IN csrSlots

That worked thank you

I can now progress from here on.

Sincere thanks

 

[Mike Lewis]

Glad to hear it's working. Come back if you have any more questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

If the question really initially only was about the grid positioning at the last record, that problem was in no way obvious from your question.

By the way: It's not the default behaviour of a grid, typically you don't create records on the fly but simply open a table or put it in the dataenvironment, which opens the table with current record position at the top, and then you don't have to GO TOP in the grid recordsource alias.

Bye, Olaf.