You could set up a new table occurrence of Services and use a modified relationship that excludes all records that have that value in the Service field. Then List ( NewTableOccurrenceNameHere::Service ) will produce the desired list.
Or, if you are using FileMaker 12, you can use ExecuteSQL to produce this list and use a WHERE clause to omit the "Travel" values.
I think the ExecuteSQL might be the cleanest option, but I can't seem to utilize it properly. I tried:
ExecuteSQL ( " SELECT service FROM Services WHERE service ≠ 'Travel' " ; "" ; "" )
But it just returns a question mark in the field. If I remove the WHERE statement, it returns the entire list of services, not just the ones selected on the client's invoice.
Use <> instead of ≠. ≠ is not a recognized SQL operator. But you'll also need either a Join clause recreating your relationship to services or an additional term in the WHERE clause that limits the records to just those with the matching Foreign key value.
Ok, I apologize. Despite your - as usual - excellent assistance, I have been thus far unable to get this to work. Can you explain a little further on what I'd need to do in adjusting the WHERE clause?
What is the relationship between your invoices table and your services table? What are the names of the match fields?
I've attached an image of my relationship graph. Thanks for your continued assistance!
Given the "chain" of relationships, adding more to the WHERE clause won't cut it. You'll need some join clauses to document the matching from Invoices to weddings to Lines to Services.
I can't guarantee that this won't result in the dreaded ? result, but cribbing from chapter 7 examples in the ODBC JDBC Guide that you can open from Filemaker help suggests this query: (but the examples don't include one for such a "chain" of relationships...)
ExecuteSQL ( "SELECT service FROM Services
INNER JOIN \"Lines\" ON Services.\"_kp_services_id\" = \"Lines\".\"_kf_services_id\"
INNER JOIN Weddings ON \"Lines\".\"_kf_weddings_id\" = Weddings.\"_kp_weddings_id\"
INNER JOIN Invoices ON Weddings.\"_kp_weddings_id\" = Invoices.\"_kf_weddings_id\"
WHERE Services.service ≠ 'Travel' " ; "" ; "" )
Note, table occurrence and field names frequently have to be enclosed in double quotes to avoid syntax errors. This is a must for all field names starting with an underscore. The \" is one way to insert a double quote character into a quoted string.
PS. your relationships that link both invoices and Lines to weddings seems to be an odd structure. It would make more sense to me to link Lines to INvoices than to weddings (and that would remove one Join clause from the above query.)
Unfortunately, it did result in another question mark. I changed the relationships as you suggested (I believe I had a reason for structuring it the way I did but for the life of me, cannot remember what that may have been...oh well, it still seems to work just fine having moved them around).
Oh, and the table I'm trying to remove "Travel" from is Contracts. But I altered the query you provided to reflect Contracts before trying it. Here's my new relationship graph:
After I thought about it a bit more, One reason for your original structure occurred to me: It made possible creating a record in Invoices for each time you bill your clients for the same wedding if you need to bill them repeatedly. Otherwise, the changed structure that I suggested makes more sense.
Oh, and the table I'm trying to remove "Travel" from is Contracts.
Hmmm, that completely changes the context and makes no sense at all to me. Your previous posts all referred to omitting data from a specific services record.
Can you post an example of what you are trying to do with this list?
I'm so sorry for all the confusion. Ok, hopefully this clears it all up:
When using the database, my typical workflow is such -
- - I create a new contact record each for the bride and groom in the Contacts table.
- - I create a new wedding record in the Weddings table, pulling the client's names from the two Contacts table occurrences. This also creates a new contract record and invoice record in their respective tables which is linked directly to that particular wedding record.
- - I add services to the client's wedding record in the Weddings table, using a portal I created which links to the Services table, which then adds those services to the Lines table, each as its own record.
- - Both the contract record and invoice record have a section which displays the services that have been added in the wedding record.
The reason I'm trying to omit "Travel" from Contracts is because of the context in which it is displayed in the contract. On the invoice, the "Travel" service stays because it is a service we are charging them for. On the contract, it displays services we are providing to the client under a section that states more or less, "our company will provide to you the following services" - and "Travel" would actually be a service that they are providing us. We don't want "Travel" listed because then it could indicate that we're providing them travel to or from a location.
- I add services to the client's wedding record in the Weddings table, using a portal I created which links to the Services table, which then adds those services to the Lines table, each as its own record.
If each record in services is specific to a single wedding as this statement seems to indicate, why do you need both a lines and a services table? Wouldn't one table serve for both?
Why should a "travel" line item even have a corresponding record in Services?
Regardless of whether or not you keep your current design, here's a simple way to get the desired list of line items that excludes any for Travel:
Put a filtered portal to services (or possibly lines) on your contracts layout. Use a portal filter expression that omits "travel" items. Make this portal many more rows tall than you ever expect to need for a given wedding. Set it to "slide up" and to "resize enclosing part".
If you'd still rather get your list function to work, you can make additional occurrences of Invoices, lines and (maybe) services. Link them as you do now but modify the last pair of relationships to use an additional match field to omit the Travel items from the list. Assuming that you keep the services table:
Lines::_kf_services_id = Services|noTravel::_pk_services_id AND
Lines::constTravel ≠ Services|noTravel::service
Define constTravel as a calculation field that always returns the same text: "Travel".
then List ( Services|noTravel::Service ) should produce the desired results.
I'm not able to figure out the second suggestion, so I tried putting in a portal on Contracts. And that worked great! I was able to remove Travel by using
Services::service ≠ "Travel"
as the filter. Is there a way to add a second or third item to that list? I tried using or, but it didn't work. Also, the sliding up doesn't seem to work. I have a basic text field above and below the portal, but the portal will still show the blank rows and the fields don't move up to fill in the blank space.
Is there a way to add a second or third item to that list?
Do you mean that you want to omit additional items?
Services::service ≠ "Travel" and Services::service ≠ "2ndITemHerel" and Services::service ≠ "3rdItemHerel"
IsEmpty ( FilterValues ( List ( "Travel" ; "2nd Item" ; "3rdItem" ) ; Services::Service ) )
Key facts about sliding layout objects:
- It's only visible in preview mode and when you print/save as PDF...
- Sliding fields will shrink but not expand.
- All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
- Objects in headers and footers will not slide.
- Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
- Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
- Consistent side borders are difficult to achieve with sliding fields.
Doh. I had only used "and" and "or" as a separator for multiple values. I didn't try repeating the entire expression for each item I want to omit. Thank you for that, Phil. Also, thanks for the clarification on the sliding objects.
I literally picked started learning FMP earlier this year completely from scratch. I had never worked with a database program before, and was pretty much going in blind. Between various web searches, a particularly helpful online web training series at VTC.com and in no small part, your replies to my several topics, I have managed to put together a fairly decent database that is a SERIOUS help to our day-to-day operations. THANK you, very much.