Portal showing empty/non existing records?

[gephry]

Hey hey,

I have a file that holds my scheduled meetings (let's say 4) per day. I would like to show, through a portal, these four meetings. But hold on, it gets more complicated.

What I already have set up: I have a portal that is set up as a relationship (Menu::Date <=> Events::Event_Date). So, of course, the portal will show all records pertaining to field Date in the database "Menu."

The problem is, if I don't have a meeting, there is no record entry in the database "Events" for that meeting because, well, the meeting doesn't exist.

What I want to do, is have the portal show those meeting timeslots, even if there is nothing scheduled at that time (remember, there will be no record for that time slot).

Is there a way that I can have the portal show all times slots (12PM, 1PM, 2PM, 3PM) regardless of whether there is a record or not?

I've seen something similar in RoomDEX by Watzman Associates. I know it's gotta be something easy or something tricky.

Thanks so much!

[Breezer]

You will need to have a script that creates FOUR records in the related table that will represent the various time slots.

Go to related table

Create four records, namely 12PM, 1PM, 2PM, 3PM

Go to original layout

[gephry]

I figured it out!

First of all, thanks for your reply Breezer. I actually tried that last night and, yes, it does work. But, creating blank records for every possible meeting time can be cumbersome. Plus, you have to set up the database to contain records reaching as far into the future as you think you might be scheduling. That affects file size, creates scheduling contraints, etc. Now, for what I have figured out...

Since I'm using 4 meeting times per day, I figured out this (FYI, gJewelHolder is a counter)

Dummy Records

1. Create 4 "Dummy" records.

2. Insert each meeting time into the Time field (12PM, 1PM, 2PM, 3PM).

3. Change the Date field to a general text field.

4. Write a loop script to insert every date into the date field (for a year, or whatever, with this format: ("Date = Date & "¶" & "12/1/05" + gJewelHolder"). That way you get:

12/1/05

12/2/05

12/3/05 etc....

for the 12PM record, the 1PM record, etc.

5. Now, these four records will show up at any time I have no meeting scheduled in that time slot.

Now, for when I schedule a meeting (FYI, "ThisDate" is the date the new meeting is being scheduled for):

Schedule a Meeting

1. Go to related record (for that certain timeslot, let's say 1PM)

2. Set Field( Date, [ Substitute( Date, ThisDate, "" ) ] )

3. Create Record, insert ThisDate into field Date, all other pertinent information, etc.

That will take ThisDate out of the Date field in the Dummy record so the record won't show up in the portal, then with the new record on that date, it will appear in the Dummy record's place.

If I delete the meeting, it will insert that date back into the pertinent record. Viola!

Again, thanks for your input!

[Ender]

An option that may work better in this case is to use one relationship for each time slot instead of the portal. Each relationship would match a different block of time (one for each of 12PM-1PM, 1PM-2PM, 2PM-3PM, 3PM-4PM) and you'd use a set of the related fields for each relationship to show the events for those time blocks. This way, no dummy records are needed.

This works well if the number of slots is not too high, and you don't have overlapping events (only the first event would show in the case of an overlap.)

[comment]

It depends on how you want to build the viewing layout, but here's a super-simple arrangement:

Table SLOTS:

Time (time field)

gDate (global date field)

Table EVENTS:

Date (date field)

Time (time field)

Details ( text field)

Relationship:

SLOTS::gDate = EVENTS::Date

AND

SLOTS::Time = EVENTS::Time

Table SLOTS holds 4 permanent records, one for each appointment time.

[gephry]

Yes, but.

How about if I throw a wrench in the works? I am working FileMaker 6. No "ANDs" in relationships.

And while we're at it, let's make this fun and throw in another. Let's filter the portal by room. I choose gRoom and I want to see all time slots (both scheduled and unscheduled) for that room. Ender, I read one of your other posts about filtering, but it also was with 8 with ANDs in relationships. The post was really good, but I'm not sure how to relate it to FM6. I've been trying to relate a Calc field (in SLOTS) to a similar Calc field (in EVENTS) that toggles dummy records in and out of the relationship depending on if things are scheduled in that time slot in the currently filtered room. But, it seems like you can't relate to a Calc field (because it can't be indexed?). I could type up the full calc with explanation if you wanted.

[FYI, this database system I'm working on is more complicated than we are currently discussing (such as the portal goes 6:30AM through 11:30PM in half hour increments---I chose 4 meeting times to make the discussion easier, etc). All the rest I already know what I need to do, or already have finished.]

Thanks all, I appreciate this discussion.

[comment]

Sorry, haven't noticed your version.

In the SLOTS file, concatenate gRoom, gDate and Time in a single calculation field (result is text).

In the EVENTS file, either concatenate Room, Date and Time in a similar fashion, or just a create a text field.

Base your relationship on the two new fields.

If the match field in EVENTS is a calculation, you will need to GTRR in order to create/modify an event.

If it's a text field, you can simply fill in the event details in the related field on a layout in SLOTS to create a new event. Here, you don't need the Room, Date and Time fields in EVENTS. Just make sure you delete an event, instead of just clearing the details field.

[gephry]

Not a problem.

I know I must be doing something wrong because I've already done what you said. I always tend to make things more complicated than they need to be. They're more fun that way. So, it's quite possible I'm overdoing it here. Okay, here goes.

I've done a calculation in each database and nothing shows up in the portal. Let me reaffirm the fact that each Dummy record (de-notated by field Dummy_Record = "Dummy") is assigned a particular time slot. Whenever I schedule something for that time slot, it adds that date to the list in the Dates_Already_Scheduled (text field). Here's what I've done:

In SLOTS:

field calcPortal_Relationship1 = gDate & "|" & gBuilding & "|" & gRoom [example: 12/6/2005|Stern|102]

In EVENTS:

field calcPortal_Relationship2 =

Case( Dummy_Record = "Dummy" and PatternCount( Dates_Already_Scheduled, SLOTS::gDate ) = 0, SLOTS::gDate & "|" & SLOTS::gBuilding & "|" & SLOTS::gRoom, Dummy_Record = "Dummy" and PatternCount( Dates_Already_Scheduled, SLOTS::gDate ) > 0, "EXCLUDE RECORD", not( Dummy_Record = "Dummy" ), Date & "|" & Building & "|" & Room )

Basically, the last formula will

1) check to see if the record (in EVENTS) is a dummy record, if it is and there isn't already something scheduled for this date (when I schedule something, Dates_Already_Scheduled = Dates_Already_Scheduled & "¶" & SLOTS::gDate), it will create the exact same string as calcPortal_Relationship1. Example: 12/6/2005|Stern|102

2) Check to see if the record is a Dummy record and if there is something scheduled for that date in that time slot, it will enter a bogus calc (for my benefit of seeing that it's working). Example: EXCLUDE RECORD

3) If it's not a dummy record, it will create the calculation (time and time again) taken from the fields within that scheduled record. Example: 12/6/2005|Stern|103 (won't show up in the portal)

So, if the calcs don't match, it won't show it. The portal automatically sorts by time, which is why I don't have that in the formula at all. I have it programmed to nix dates from the dummy-time-records.

Do you follow?

So, the problem is, after all those formulas, things match through the relationship, but no records show up through the portal.

There's either something small, or something about my approach.

What is GTRR?

[comment]

Do you follow?

Uhmm, not really. I am trying to say that no dummy records are required. In fact, the whole idea of relational database is opposed to the notion. The records in EVENTS should only represent actual events.

To display events against slots, you simply display the slots. Each slot shows its related event - if it has one.

Though I don't quite understand your calc, it seems that you've tried to use related fields in order to calculate the key field in EVENTS. That won't work - a match field in the child file must be indexable.

Building a calendar-type display can be quite complex (even frustrating, at times) - especially in pre-7 versions. I would suggest you keep it simple to begin with, and do not try to integrate everything at once.

GTRR - Go to Related Record (script step)