How to show found records on a layout with cond formatting

[***ANDY***]

Hi All,

I am stuck with a conditional formatting issue on one of my layouts. Here's what I'd like to accomplish:

I run a business for motorcycle tours. I have a total of 20 tours for 2012 with a maximum of 15 participants for each tour. For an overview of what is booked and how many spaces I have left over for a soecific tour I want to have a layout that graphically shows me how many spots on a scale from 1 to 15 are sold and how many are still available. In the attached file I'm showing a graphic example of what I want the solution to look like. Each red square shows a sold spot on the tour while the white squares show the spots that are still available.

I'm not looking for a complete solution, just a hint of what direction to go to. I guess each square needs to represent one record = customer in my database. A trigger behind the date pull down field would be used to initiate the find records script but how do I convert each found record into one of the squares?

Thanks to all,

Andy

tours_sold.zip

[dansmith65]

The short answer to the exact question you asked might be to use this for the conditional formatting calculation.

Get( FoundCount ) >= [box number]

Although, I don't know if that will even work because I'm not sure how you've structured your data, or where this found set of records is. To delve into this a little deeper, you should probably have these tables in your database:

customers

tours (each record is a particular tour 'Route 66 on a particular date '6/28/2012')

tour attendees (contains tour id and customer id)

Do you have those tables in your database?

P.S. when you attache an image, don't zip it, because you by-pass the nice image viewer the forum uses

[Agnes Riley]

Hi Andy,

I'm a motorcylce rider. I can throw in half an hour to help you out via screen sharing if you like. Message me through the back and we'll set something up.

Best,

Agnes Riley

Hi All,

I am stuck with a conditional formatting issue on one of my layouts. Here's what I'd like to accomplish:

I run a business for motorcycle tours. I have a total of 20 tours for 2012 with a maximum of 15 participants for each tour. For an overview of what is booked and how many spaces I have left over for a soecific tour I want to have a layout that graphically shows me how many spots on a scale from 1 to 15 are sold and how many are still available. In the attached file I'm showing a graphic example of what I want the solution to look like. Each red square shows a sold spot on the tour while the white squares show the spots that are still available.

I'm not looking for a complete solution, just a hint of what direction to go to. I guess each square needs to represent one record = customer in my database. A trigger behind the date pull down field would be used to initiate the find records script but how do I convert each found record into one of the squares?

Thanks to all,

Andy

[***ANDY***]

The short answer to the exact question you asked might be to use this for the conditional formatting calculation.

 Get( FoundCount ) >= [box number] 

Although, I don't know if that will even work because I'm not sure how you've structured your data, or where this found set of records is. To delve into this a little deeper, you should probably have these tables in your database: customers tours (each record is a particular tour 'Route 66 on a particular date '6/28/2012') tour attendees (contains tour id and customer id) Do you have those tables in your database? P.S. when you attache an image, don't zip it, because you by-pass the nice image viewer the forum uses

Hi Dan, I have one table containing all customer records and another table containing all tour dates. I don't have a separate table for the tours or tour attendees. It looks like I need to restructure my database.

If I execute a script that finds me e.g. 10 records (= customers) for one particular tour date, then how am I going to assign each single record to one of their referring boxes (which are fields so I can conditionally format them)?

Also if I set one record to cancelled the filed should turn back to white so I can see that this spot is available again.

PS: I had zipped the file as I thought all files needed to be zipped in order to upload them. I tried to upload a fmp file the other day and I couldn't do it unless I zipped it.

[comment]

I have one table containing all customer records and another table containing all tour dates. I don't have a separate table for the tours ....

If you never have 2 or more tours on the same day, then the table of tour dates IS a table of Tours. Otherwise you are in trouble.

... or tour attendees

What do you do when a customer wants to participate in more than one tour?

[***ANDY***]

I don't have more than one tour on the same day, more than one tour of the same kind that is, like 2 Route66 tours for example.

Since a customer can't physically take part in more than one tour at the same time I create a new record if for example he wants to do tour #1 in May in tour #2 in September.

I know this leads to double entries in the customer table and should have probably done differently in the first place when I started as a total novice with FM5. But I can live with how it is right now; would I build it different if I'd start all over? Certainly yes.

[comment]

I don't have more than one tour on the same day, more than one tour of the same kind that is,

So if you have a customer for a tour on March 15, and there are two tours on that day - how do you know which one the customer wanted?

[***ANDY***]

So if you have a customer for a tour on March 15, and there are two tours on that day - how do you know which one the customer wanted?

When a customer books a tour he cannot just chose any date for that tour as all tours are scheduled at pre-set dates. I have one field which contains all tour names. If I select a tour name a drop down list in a different field opens with all available dates for the selected tour.

[comment]

If I select a tour name a drop down list in a different field opens with all available dates for the selected tour.

If you can do that, then your table of tour dates also contains a field for tour name, doesn't it?

[***ANDY***]

If you can do that, then your table of tour dates also contains a field for tour name, doesn't it?

Yes it does and every tour with its according date is on a separate record.

While contemplating over this I was wondering if there is a plug-in where I could show the same thing with a pie chart or horizontal chart bar...with my FM10 version I don't have the built in feature.

[comment]

every tour with its according date is on a separate record.

That's good - and that's a Tours table by all counts.

I wonder if something like the attached could suit you.

ChartR.zip

[***ANDY***]

This would do the trick just fine! Does the red field on record two simulate a "cancelled customer"?

[LaRetta]

It is showing that record 2 is overbooked. There are four Tourists but the tour size is only 3. So yes, you will need to cancel a customer or expand your tour!

That is nice technique, Comment!

[***ANDY***]

It is very nice technique and I'm currently trying to rebuild and adjust it to my needs. Thanks comment, it's an awesome solution! Giving me lots to think about...