Access code to FileMaker

[Kevin Cheesman]

This is the code I use to create a database of dates. Can someone tell me how to do this in Filemaker?

Thanks.

Function filldates2(sdate As Double, edate As Double)

Dim i As Double

Dim j As Double

Dim db As Database

Dim rs As Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("TblDates", DB_OPEN_DYNASET)

Dim numdates As Double

numdates = edate - sdate

For i = 1 To numdates

For j = 1 To 2

rs.AddNew

rs!Date = sdate + i

rs!SessionLength = IIf(j = 1, "AM ONLY", "PM ONLY")

rs.Update

Next j

Next i

rs.Close

End Function

[Ender]

Can you tell us in English or Pseudocode what you want to do?

[comment]

I think this is a duplicate post.

[Kevin Cheesman]

The code, which I guess is simpler in FileMaker than in Access, should allow me to generate a table just containing dates. I want to be able to enter 2 dates (1st Jan to 31 August for example) and then generate a table containing all the dates from and to those dates. Once joined to a booking table and viewed as a table, this will show me what dates are still free and what dates are booked, which is better visually when you are trying to find a free date while on the phone to someone.

[T-Square]

Your original code includes some obfuscations--what's with the AM/PM? Setting that aside, I will simply address the direct question, which is how to turn your code into a FileMaker script.

First, you will need a way to handle sdate, edate, and a counter. Method A (usable in most versions of FM) uses global fields to store these values. Method B (useable in FM7 and later) is to use a Script Parameter. Method C (valid in FM8) is to use variables. Because I am most familiar with Method A, I'll use it (note it's also the most unsavory WRT database design/programming precepts). You can look into the other ways at your discretion.

To create the global fields, go into Define Database and add an sdate and edate fields to the table in question. Each will be a date field that uses global storage. Global field gCounter will be a number field using global storage.

With the fields created, create a means of populating them. You can either have a custom dialog box with both global fields on it, or a specific layout with them on it.

Having established the fields, your script could look like:

FillDateRecordsScript:

Go To Layout["TheLayoutForTheRightTable"]

Set Field["gCounter"; 0]

Loop

Exit Loop If [sdate + gCounter = edate]

New Record/Request

Set Field[realDate; sdate + gCounter]

Set Field[gCounter; gCounter + 1]

Commit Record

End Loop

No doubt others will offer different approaches, but I hope this sets you on your way.

David

[Kevin Cheesman]

Hi David,

Thanks for the help. This is just what I needed to get me started.

The AM/PM bit is just that I can only ever take on AM, PM or all day bookings. As I rewrite my system I may find a better/diffeent way of doing this.

Kevin

[Kevin Cheesman]

Sorry as a real newbie to FileMaker, I got so far but the code is looks different in 8.5 to what you've put.

eg. I can only enter Set Field [Test::gCounter = 0]

and I can see what the realdate bit and how to enter it.

Kevin

[T-Square]

Kevin--

AFAIK, the Set Field command is the same in 8.5 as it is in 7 (but obviously, since I'm on 7 I can't say for sure). Perhaps you have not checked the "Specify" option for the Set Field script step?

WRT "realdate", that was my way to say "set the field in your database table that is supposed to hold your 'real' date for each entry of the result set." So, if your db table uses a field called "ApptDate", set THAT field.

Your original code set a db column named "date." You REALLY don't want to use "date" as the name of a field in your table (in ANY database system). I guarantee that you'll at the least be confused in development when you try to remember that "date" is the name of your field AND the name of a command and datatype. Moreover, you'll have severe troubles if you want to use ODBC.

That was why I used the made-up name of "realdate."

HTH,

David