'find' query in portal (newb question!)

[mrsimmo]

Apologies - I *think* this is a portal question, but it might be that something more fundamental is amiss in my approach... Apologies in advance..

 

I am working on a scheduling management application for a festival. We collect submissions from artists in a webform in which they also indicate their availability within the 8 days of the festival. 

Currently this is expressed as a single text field generated by the web form, users are given the option to select am, pm and evening (the broad structure of the festival day) - so the result is a field something like "Mon AM, Tues PM, Sat AM" for each submission. 

 

What I'm trying to create is a schedule form whereby i can see all of the available events on a given day, and then create them as *scheduled* events within our database based on their availability. 

 

Whilst I can filter the submission records as "find where availability = mon", what I want is to be able to see them all on the same screen, so I can enter them as events into our db based on the available pool. 

 

I was thinking that creating a portal would be the way to do this, but can't for the life of me figure out how to view all of the available events on (for example) a monday in it. 

 

I suspect this might be a structural problem rather than a portal misunderstanding, but I'd really appreciate any help... 

I've attached a screenshot of my current basic schema, where the 'events' table are the public submissions and the 'schedule' table will be the 'actual' festival records.

 

Apologies - I *think* this is a portal question, but it might be that something more fundamental is amiss in my approach... Apologies in advance..

 

 

 

 

Many thanks indeed for your help in advance!

-i

[Matthew F]

You might want to edit your duplicated text, above.  

I was thinking that creating a portal would be the way to do this, but can't for the life of me figure out how to view all of the available events on (for example) a monday in it. 

On what table/layout are you wanting to put this portal?  What happens when you try?  

[comment]

the result is a field something like "Mon AM, Tues PM, Sat AM" for each submission.

...

how to view all of the available events on (for example) a monday in it.

 

Ideally, each submission would have multiple related records in an Availability table. The way you have it now, you can do one of the following:

 

 

1. Define a calculation field in the submissions table (result is Text) =

Substitute ( Availability ; ", " ; ¶ )

Using this field as the matchfield in a relationship will allow you to use a text field on the other side with, say "Mon AM" in it and show all submissions that have this text in their Availability field. Note that the match has to be full - "Mon" alone will not link to "Mon AM". I am also assuming that the text used to describe a specific time slot is uniform, and so are the separators.

 

 

2. Use a filtered portal based on a relationship that gets all submission records (using the x relational operator). This is slower, but more flexible: you can set the portal filtering calculation to show records that contain some phrase, e.g. "Mon".

 

 

In both cases, you will want to use an additional occurrence of the submissions table for this auxiliary relationship, and leave your core structure as it is.

 

 

---

P.S. You say the festival lasts 8 days. That's bound to make at least one of the time slot descriptions ambiguous.

[mrsimmo]

Thanks so much for the replies, folks. 

 

I've taken amended the original post, Matthew. I'm afraid it was a bit late at night when I was writing it - apologies  

 

I've taken your advice comment, and added a new availability table - clearly something I should have done before. So, now - I'm able to see all of the available events on a given day by using a series of filtered portals, using the x operator and each on a different tab. This appears to work *great*! I've attached a new screenshot of my tables, does that make better sense now?

 

Thanks so much for your help... !

 

 

 

 

[comment]

Uhm, that's not quite what I meant. There is no advantage in having an Availability table, if you're going to have a field for each time slot. The table should look something like:

 

• AvailabilityID (auto-enter serial number)

• EventID

• Day (e.g. "Mon")

• Time (e.g. "PM")

 

Then looking from Schedule through a chain of relationships like this:

 

Schedule -< Availability >- Events 2

 

where Events 2 is another occurrence of Events, and the relationship between Schedule and Availability is:

 

Schedule::Day = Availability::Day

AND

Schedule::Time = Availability::Time

 

you can see all events that are available for the current schedule slot by placing a portal to Events 2 on a layout of Schedule. Note that this is assuming the Schedule table has corresponding Day and Time fields (unlike what your RG shows).

[mrsimmo]

ack- yes I see..

 

Although - the reason I had multiple fields for each broad slot, is that some events might be available to happen at a number of times - Mon PM and Weds AM for instance. I understand that this method would make more sense though.

 

Thanks again, much appreciated...

 

I'm getting there.. I think...

[comment]

events might be available to happen at a number of times

 

That's what I meant when I said that "ideally, each submission would have multiple related records in an Availability table." Ideally, meaning providing the most flexibility. For example, it's the only way you can record a note regarding a specific availability, such as "we are available on Mon PM, but only from 7:00 PM or only in the western part of the town".

 

In many cases, a less than ideal arrangement can be sufficient. For example, if you would have a checkbox field in the Events table, using a value list of Mon AM, Mon PM, Mon Eve, Tue AM, etc., you could then establish a relationship directly to the Schedule table using this field. Note that a checkbox field lists the checked options a return-separated list - and a relationship using a return-separated list is an OR relationship: http://www.filemaker.com/13help/en/html/relational.11.4.html#1027684

 

This is why I initially suggested turning your comma-separated field into a return-separated one.