Lost booking dates in a diary

[Kevin Cheesman]

I have a table of dates (one record for each date from Jan to Dec) and a table of bookings, which will be various dates and may have 2 or 3 booking on the same day. I can get the dates to display in order on a new 'list view' layout and as I scroll down, bookings show for their related date, as that's what they are join on. However, where there is more than one booking for a particular date, other records will not show.

I did do this in Access and achieved it with a query. I had the diary dates repeat showing all the bookings. i.e.

01/02/07

02/02/07 A booking

02/02/07 Another booking

03/02/07

03/02/07 Yet another booking

04/02/07

I want it displayed this way as it's easy to see which dates are free. I can't see how to get the same result in FileMaker as it's all a bit different to Access.

Can anyone help?

[Søren Dyhr]

I came to think of:

Go to Layout [ “AllDates” (AllDates) ] 

Go to Related Record [ From table: “Bookings”; Using layout:“Bookings” (Bookings) ] [ Show only related records; Match found set ] 

Go to Related Record [ From table: “AllDates”; Using layout:“AllDates” (AllDates) ] [ Show only related records; Match found set ] 

Show Omitted Only 

...since you're on fm8!

--sd

[Kevin Cheesman]

No, this does not work.

Would it work with another table instance and different relationship?

[comment]

The only way for this to work in Filemaker exactly as in your example is to have a table where each one of these is a record:

01/02/07

02/02/07 A booking

02/02/07 Another booking

03/02/07

03/02/07 Yet another booking

04/02/07

Since I don't suppose you want to import records from both tables into a third one every time you want to view them, I'd suggest you display them in some other way - for example a list view of days with a portal to bookings, or (that would be my preference) a calendar-type layout:

http://fmforums.com/forum/showtopic.php?tid/176396/

[Søren Dyhr]

What is your present relational stucture then since it won't? You have hopefully noticed that the GTRR was the new feature arriving with fm8

--sd

[Kevin Cheesman]

I've related them the date fields i.e. the single date in the dates table and the booking date in the bookings table. I guess this is why it is only showing one record. Looking back at how I done this is Access, I just joined the tables and it all worked. I need to be able to show more then one occurrence of the booking date when it is presented on the layout.

[Søren Dyhr]

Does table-view or list view mean anything to you? What does the rolodex in the status area say "150 of 153 record" or something in the vicinity. I havn't got the faintest idea how Access does anything, but if more records are in the found set, will the data then be presented in a list?

--sd

[Kevin Cheesman]

I'm using a layout in list view.The source table is 'dates'. For now, I only have 2 fields: one from dates and one from bookings (customer name). The tables are related on date. The date field displays in a list from top to bottom, but only the first related date from my booking table shows. So, how do I get the other bookings which are on the same date to show in this layout?

[Søren Dyhr]

Are then are we solving two differnt matters, I thought you would display a list of un-booked days.

If you take a look at the above, is it a subsummary report, made in appointments ... are we any closer?? The search before the reporting would then be an omitted search on a binary field called completed.

--sd

[Kevin Cheesman]

Hi,

Yes, this is the sort of thing I need. I may have more then one booking for a particular day - one am and one pm. Having booked and free days listed makes it very easy to see free dates.

[Søren Dyhr]

Well the empty dates won't show up in the Sub summary report, but you could instead do it in a calc'field using this CF:

http://www.briandunning.com/cf/534

Or this approach:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

But there is a kind of weirdness in your reasoning, databases are when things work to be considered vessels of meaning, and not eyeballing opportunity provider... if spotting empty slots not needs to be found computerized, could other means be instated to let the entire organization know about each others way abouts:

http://www.google.com/intl/en/googlecalendar/tour.html

While a genuine database does something different, say like this:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

[comment]

Well the empty dates won't show up in the Sub summary report, but you could instead do it in a calc'field using this CF:

http://www.briandunning.com/cf/534

Care to show how?

[Kevin Cheesman]

I've use a portal for now. Not ideal, but it will do until I get my head things.

In Access you can create fields that only exist from a query. I can't see how you do this in FileMaker.

[Søren Dyhr]

You mean the opposite way, but the most atomic records of the two tables is appointments? Seen from the other side would I probably use the pseudo-portal method, with sliding ... no summary fields. Or do you double the breakers ... isn't there an issue here:

http://network.datatude.net/viewtopic.php?t=128

--sd

[Fenton]

As comment says, create a table with dates, 1 per day. Easy to create with a looping script. Create a relationship based on date to the Booking table. Either put a portal in a list view (if 3 or so is the max). Or better for printing, use a function such as List() to gather the related events into one return-separated field.

It looks like you just need to return 1 field, the event, so List (date relationship::event) would work; but if multiple fields are needed in the result, concatenate them first, or use a Custom Function, such as the one mentioned, or GetRows.

GetRows

http://www.briandunning.com/cf/309

also requires GetNthRow

http://www.briandunning.com/cf/308

View from the Dates table if you want to see multiple events and empty dates. You could also view from a single Event, if you wanted to see dates; you could create another relational filter to see only empty dates, or show them in a drop-down Value List. That's different from what you were asking however.

[Søren Dyhr]

Yes! I just realized that the CF I linked to is faulty in it's behaviour, Bruce's CF's does what actually meant. But there isn't so far as I can see a subsummary way to accomplish it??

But basicly does the Pseudo portal method the same, but yikes it includes a repeating calc.

--sd

[comment]

But basicly does the Pseudo portal method the same, but yikes it includes a repeating calc.

Pseudo-portal, portal or list - there is no way to eliminate empty rows (or empty space) in Browse mode.

As comment says, create a table with dates, 1 per day.

Just for the record, that is not what I say. I have an aversion to tables with no real information in them. I would go with some type of calendar view.

[Søren Dyhr]

there is no way to eliminate empty rows (or empty space) in Browse mode

You know I always think it fun to challenge your wisdom, not that I entirely can explain why it seems to behave, but here it goes!

--sd

wisdomPureLuck.zip

[comment]

: I have no idea what you are trying to show here. Perhaps you should scroll back to the original question?

[Søren Dyhr]

That empty portal rows via List( could be removed and turned into a repeater which would allow sliding objects.

--sd

[comment]

I said "in Browse mode".

[Søren Dyhr]

then look at the repeating field in browsemode as a long pilcrow delimited string of mergefields!

--sd

[Kevin Cheesman]

I think I'll be happy with pulling out the results and using mergefields with sliding. It's only for an indication as to what free dates are available.

Now, what page was that on in my FileMaker book?

Thanks for the help everyone.

[comment]

then look at the repeating field in browsemode as a long pilcrow delimited string of mergefields!--sd

But you still need to reserve space for them [color:red]in List view.

If I thought a simple list (i.e. not something you can click on to go to the relevant record) would be enough, I would do something like this (attached). But I don't quite see the point of this - surely it is easier to eyeball a calendar?

FillBetweenDatesCF.fp7.zip

Edited January 22, 2008 by Guest
replaced attachment

[Kevin Cheesman]

FillBetweenDates is just what I had in Access. Perfect!

[Søren Dyhr]

It's very good, but it inserts a series of appointments for the day before the date, but leaves the date there ... shouldn't the orignal date be removed?

--sd

[comment]

I don't know Access, but I believe you're wrong about that. I think what you had in Access was a "full outer join" of the two tables. The difference is that with a join, every row of the result is still a record (or a "virtual record") in the original table/s, while what you have here is a plain textual list with no tie back to the originating record.

Filemaker doesn't support all types of joins possible in SQL. To get a truly similar result, you would need to import non-matching records from both tables into a third one.

[comment]

Yes, that was a mistake - I have fixed it now.

[Søren Dyhr]

So you have - excellent!, a very good catch to look at the following date in the ListOfEvents variable, becasue what I thought would have worked was to put:

Case ( startDate + 1 = itemDate ;startDate & ¶ )

...in the last line ... but only to learn that the strategy broke when apointments were on dates next to each other say the 12th and 13th

--sd