Filtering/Sorting by relationship within a table

[trumans]

I run salon/spa where clients need reservation reminders. On a given DATE, a CLIENT has a TICKET with multiple SERVICES and multiple STARTTIMES

Goal: I want to be able to select a DATE, and have the found set show the first STARTTIME for a given TICKET. I will then send a reminder email for that reservation time. Right now I can only get all services and all start times to show, so I have to manually go in and select the record with the first start time. Can anyone help (I am a newby, so please be specific). Thanks

[mr_vodka]

See if this files helps you get going.

SpaServices.zip

[trumans]

Thanks for the response. Is there any easy way for you to give me a tip on the type of calculation/field that I should create? I see that your model works but I cannot see how you did it.

Thanks

[mr_vodka]

There are no calc here. Everything is done through relationships. Basically, there are 4 tables here: Services(Product), Clients, Ticket, & the Line Items for that ticket. You can think it in terms of an invoice...

On an invoice, you have the client's info, the general info of the invoice such as date, etc., and then qty, price, subtotal, etc for a particular Item. The items itself with the base price is stored in its own table. The productID code it used much like a receipt.

So if you relationship of related line items is sorted by time in ascending order, the first record will be your earliest. The portal displays all the related records on the associated line items for a particular ticket... If you happen to put a related field on a layout it will display the FIRST related record. In your case, since it is sorted Ascending, it will display the record with the earliest time.

[trumans]

That makes sense . . I have additional challenge in that the imported data does not always come in sequence of time. In other words, a ticket may contain 3 services starting at different times, but they may not necessarily be listed in chronological order. I can replicate what you have done to show the start time of the first service (using sorting) but i am also using a script that sends an email to each client. Though I can create a field that will find the first start time per ticket, I am still left with all of the records for each service in the table so my email script would send 3 emails. Is the next step to update my script so that it does not send multiple emails to the same person or is there a cleaner way o do it?

I really appreciate your help!

J

[mr_vodka]

If you have your file set up the way that I have in my sample, then you can send one email per each ticket. There is no reason to send it for each line item.

Since you are on FileMaker 8, you can not make use of the list() function. So instead, you can have you emailing script to run a loop and set a temporary global field to store each appointment line item detail.

Then this one global field should be included as part of the one email. Resulting in something like:

Dear Mr. McCain,

You have an appointment with us at 9:00 AM on 3/20/2008 for the following services:

09:00 AM - Massage

11:00 AM - Facial

12:00 PM - Manicure

01:00 PM - Pedicure

Thank you.

[trumans]

As you may have guessed, I have been trying to find a way to do this within one table (using self-joining relationships and sorting). I was resisting setting it up exactly as you had done because I already have other tables to store other client and service data and thought it would be less complex to do this within one table. As a side, I couldnt see an easy way of importing one daily excel file (tickets, times,services, client names, emails, etc) into multiple tables at one time. Is a multiple table format the only way you know of? If so is there an easy way to import the data from one spreadsheet into multiple tables all at once?

[mr_vodka]

you would have to do multiple imports. If you are setting this up in FileMaker, then why are you doing it in Excel as well?

[trumans]

unfortunately the data comes out of a proprietary salon software program . . I export to Excel and then import to FMP

[mr_vodka]

Technically, you could try and have all the appointments and ticket info in the same table, and as you said, using self joins etc, you could possibly do it... However that being said, it would be a lot of extra effort for something that could be done so much easier if properly normalized.

[Fenton]

trumans, could you post a small example of this Excel file? As David says, this could be done with self-joins, since your data comes in "flat." Or you could run a script to copy the data to a better relational structure. Yes, a bit more trouble, but then you could use it properly afterwards. If it remains flat it's going to be awkward, you could Sort it, then look at the 1st record for a ticket. But that's not much better than Excel.