Creating "complex forms" in FileMaker - is it even possible?

Question

I have been asked to look into FileMaker for creating a pretty simple database app. The application will handle contact information, some information about events hosted by the organization and - and this is where I'm currently struggling - RSVP information that link the contacts and events, as well as stores some data about payment.

What I would like to use is some kind of form where the user gets to search for a contact (any combo of first/last name) and an event (any combo of name/date), select each from two respective lists (where all other information is displayed as well, to distinguish the results), add some extra information and hit submit.

The closest I've gotten so far is a form where the user can enter a ContactId and EventId manually, which means that he/she first has to go to another view, search for the records, and copy/paste the id numbers.

• Is there really no way to get closer to my vision using FileMaker?

• Would a better option be to build a new, custom app using for example C# and MsSQL?

• If so, how do I sell this to my contractor? As this would in that case be my first commercial application, there is obviously a "safety factor" that speaks in favor of an established product. And then we haven't even mentioned that the cost would probably increase, as developing a new app from scratch would take much longer time.

Note: I have no previous experience with FileMaker. I've tried to read the documentation, but I haven't been able to find any tutorials that take me closer to my specific needs. I'm fairly experienced in MsSQL, so I do know this and that about database management in general - just not in FileMaker.

Answer 1

There are loads of ways to do it. This is a quick way to get it to work.

Let's say you have two tables like this:

Contacts     Events 
--------     --------
ContactID    EventID
FirstName    EventDate
LastName     EventDetails

Create a new link table between them that also stores the extra RSVP information you want.

RSVP
--------
fk_ContactID
fk_EventID
PaymentInfo

Create a FORM table

FORM
--------
ContactSearch
cContactMatch = Calculation, If(isEmpty(ContactSearch) ; "ALL" ; ContactSearch)
EventSearch
cEventMatch = Calculation, If(isEmpty(EventSearch) ; "ALL" ; EventSearch)

Add the following fields to the Contacts and Events tables:

Contacts
--------
cMatchField = Calculation, Stored, (FirstName + NEWLINE + LastName + NEWLINE + ALL + NEWLINE + Firstname LastName) 

Events
--------
cMatchField = Calculation, Stored, (EventDate + NEWLINE + EventDetails + NEWLINE + ALL)

This means that the cMatchField for Contacts will look something like this:

John
Smith
John Smith
ALL

In the relationship diagram, connect the tables like this:

FORM
--------
cContactMatch    =   CONTACTS/cMatchText
cEventMatch      =   EVENTS/cMatchText

Create a layout called FORM based on the FORM table.

Add the fields ContactSearch and EventSearch to the layout. Add the PaymentInfo field.

Add two PORTALS to the layout, one for the Contacts table, one for the Events.

By default you should see all the records in each of these portals.

Write a script, or use a script trigger, that refreshes the layout whenever one of those search fields is Exited/Modified. This should refresh the portals and show you the related records you're interested in.

Add a button to each row in the portals and call a script that sets a global variable to that portal rows ID.

For example:

Script: Set Selected Contact ID
Set Variable ($$ContactID ; Contacts::ContactID)

Script Set Selected Event ID
Set Variable ($$EventID ; Events::EventID)

Add another button to the layout and a new script.

Script: Create RSVP
# Check that a contact and event have been selected
If(isEmpty($$ContactID) or isEmpty($$EventID)
    Exit Script
End If
# Get the payment info that has been entered
Set Variable ($PaymentInfo ; FORM::PaymentInfo)
# Create the RSVP Link record
Go To Layout(RSVP)
Create New Record
Set Field(fk_ContactID ; $$ContactID)
Set Field(fk_EventID ; $$EventID)
Set Field(PaymentInfo ; $PaymentInfo)
Commit Records
Go to Layout (Original Layout)
# Clear the search fields
Set Field(PaymentInfo; "")
Set Field(ContactSearch; "")
Set Field(EventSearch; "")
Set Variable($$ContactID; "")
Set Variable($$EventID; "")
Commit Records
Refresh Screen

Phew.

And you should be back, ready to search for Contacts, Events, and "Submit" the form to create more RSVPs.

FileMaker is fun, eh?