Nested Fields
Hello,
I am working wit a database that requires certain fields to be filled out based on certain criteria.
For example:
If the event fees box is checked to yes or radio button yes is selected then I would like it to display the fields to
take credit card information. If the box is checked to no then I do not want to display the credit card information fields.
Any ideas?
Another option is to use a calculation field:
If(radiobuttonfield = "Yes", Creditcardfield, "") /* this calc assumes that radiobuttonfield and Creditcardfield are in the same table as the calc field */
This is simpler and easier if you just have a small number of fields that you only need to display and not edit.
If you need to edit the data now visible or have a larger number fields to display, using the previous, relationship based approach will most likely be the better way.
Ok I have and Events table and a ConferenceDetails table. One field in the events table is called EventCategory now this is the calculation that I have so far.
if(EventCategory = "Conference"; Conference Details)
But I am usure if this is how to do it correctly.
An If function has three parts (parameters).
Part 1 is an expression that must evaluate to true or false.
Part 2 is an expression that will be evaluated only if part 1 is true.
Part 3 is an expression that will be evalutated only if part 2 is true.
You need the third parameter, an empty string, "". That's what makes the field seem to disappear.
if(EventCategory = "Conference"; Conference Details; "")
I recall that you mentioned both checkbox and radio button formatting. If you use a check box with more than one value, be careful. You may have to use a more sophisticated expression than just field = "value" in that case.
I think I'm still misunderstanding the concept of what's going on here though. This is what I am trying to do. In the Events Table there is a field called EventCategory which is a drop down list with 5 events listed. If "Conference" is chosen I want to display the fields that are in the ConferenceDetails table because those fields are specific for Conferences only.
In the Events table I have ID set as the PK and in the ConferenceDetails table I have EventsID as the FK.
So with the if statement that I created my line of thinking went like this:
if(EventCategory = "Conference"; go to conference details layout)
Hi Phil
PhilModJunk wrote:
You need the third parameter, an empty string, "". That's what makes the field seem to disappear.
this isn't any more true ( from 7 )
Some time it takes several posts to clarify the issue. I assumed you wanted a small number of fields to appear and disappear from the current layout. You now indicate that you want to take the user to a different layout based on the choice the user makes from a drop down list. Is this correct?
You can do that but the solution is quite different. You'd need a script to do that with either a script trigger (filemaker 10) or a button (earlier versions) to trigger the script after the choice has been made.
Please confirm which option you want and I'll take it from there.
Well, I'm looking for the best way to meet the needs of this database. I am creating an event database that 10 people can go in to and enter information about their events. So far I have created xx tables. The first table is an Events Table where a person can create and Event (conference, workshop, visit, etc.) and specify the date, location, and the hosts contact information. The PK of the Event table is a serialized number
All the fields are the same except if the event chosen is a "Conference". If Conference is chosen then there are 7 additional fields that need to be filled out. Right now I have the 7 additional fields in a separate table called ConferenceDetails.
The next table is the Attendee Information table. This table takes in the attendees Name, mailing info, phone #, etc. becuase we have alot of repeat visitors it eliminates the need to re-enter them. The PK of the Attendee Table is also a serialized number.
The next table is called Payments. This is where the check box/radio button comes in to play. If an event has fees associated with it (yes/no) then i would like for it to display the Payment fields.
Also, I am planning on combining info from all the tables into a Participants table that will display an Event and its attendees. When there is fees associated with an event it will also display whether or not that attendee has payed.
Lastly, each Event will have a portal that will display its attendees, and each attendee will have a portal that displays the events they went to.
Hopefully this helps in showing what this project entails. I wuld love to hear any suggestions that you may have.
Thanks for the info. It's really a question of layout design and your personal preferences. If you have room on your layout to display the conference details, you can have an area that is blank unless the user selects "Conference" from the event type. If you don't have room or don't want this "blank" area on your layout, you can switch the user to view a different screen of data that shows the conference detail fields. Both options can be done and in a variety of ways.
When I re-read all your posts, I think you want the first option. In addition, the calculation field approach that I described in my second post and that you've been trying to use isn't the best option. As I stated in that second post, that approach is good for one or two fields that need to be displayed but not edited. In your case, you need to fill out the fields once they appear.
Let's back up to the beginning here. We can set up a relationship that will control the appearance/disappearance of your details fields.
I'm assuming that you already have the following relationship: Events::EventID--=--ConferenceDetails::EventID. Make sure that this relationship does NOT have the "Allow creation of records via this relationship" option selected for the ConferenceDetails table occurrence. (I didn't see where you named your PK field, so I'm calling it EventID).
Now place a portal on your layout and choose ConferenceDetails in the "Show related records from:" menu. You can set up this portal to show just one related record and resize it into a larger rectangle than the narrow row that is automatically created. If you add fields to the portal when you first create it, the field names will be left out. You can add these after the fact using the layout text tool or by double clicking each field and checking the Create Label check box.
Now use Scripts | Manage Scripts to create a new script:
#On Modify Events::Conference
#This script creates a related record in the ConferenceDetails table
If [Events::Conference = "Conference"]
Freeze window
Set Variable [$EventID, Events::EventID]
Go To layout [ConferenceDetails (ConferenceDetails)] //You can use any layout here that refers to the ConferenceDetails table
New Record/Request
Set Field[ConferenceDetails::EventID, $EventID]
Go to Layout [original layout]
End If
Define an "On Modify" script trigger for your Events::Conference field that you've formatted as a drop down list and select the above script as the script to be performed.
How this works:
Normally we'd set up the portal's relation to automatically create related records when the user starts entering data into a portal's field, but you want the fields to be invisible until a user chooses "conference" from the drop down list. The script creates the related record for you. The related records all the fields you've placed in the portal will thus "appear" only if the user selects "conference" from the drop down list.
Sorry, Script triggers are an FMP 10 feature. Older versions don't use it.
Follow the same instructions above, but place an "Edit" button to the right of your drop down menu and set it to perform the script. This is not as nice as a script trigger, but it'll work.
I upgraded to FMP 10 to take advantage of the script triggers because they will be very useful in the rest of the database that I am creating. I created a script and a script trigger, but when I create a new record and choose Conference from the drop down list the portal does not display anything. This is what I have for the script:
If[Events::Event Category = "Congerence"]
Freeze Window
Set Variable[$EventID; Value:Events::EventID]
Go To Layout["ConferenceDetails"(ConferenceDetails)]
New Record/Request
Set Field[ConferenceDetails::EventID]
Go to Layout[original layout]
End If
And in my portal i have the following:
A 1x1 portal with these fields stacked horizontally:
Submission Date
Approval Date
Conference Organizer
Conference Organizer Group
Conference Organizer Mail
Conference Organizer Group Ph. #
Conference Organizer Email
Is there something that I have done wrong?
It looks OK to me. You have Conference misspelled in line 1, but assume that was a typo when you posted the script.
Does it work for you?
If it doesn't, what does happen when you select "conference" from the drop down menu?
That could be a problem with the relationship or the layout may not be refreshing to show the related detail record. Do the fields never appear, or will they appear after you open and close the database, or switch to layout mode and back to browse? If yes, that's a refresh issue.
I'm guessing you may have a relationship that needs fixing or your portal's setup needs updating.
Check the name of the table occurrence listed in the top of the portal setup dialog. (Double-click the portal while in layout mode to open the dialog.)
Open Manage | Database | Relationships
Find the same table occurrence listed in your portal
Double click the line linking this table occurrence to your main table and describe in detail what you see.
What fields are listed as key fields?
What table names?
I tried doing the refresh things and it did not change anything. The fields that are listed as keyfields are:
Table: Events Table: COnferenceDetails
EventID = EventID
In the Edit Relationship window under ConferenceDetails there are three checkboxes
1. Allow creation of records in this table via this relationship
2. Delete related records int his table when a record is dleted in the other table
3. sort records.
Should I have any of these boxes checked because right now i do not have any checked.
TTiger wrote:I tried doing the refresh things and it did not change anything.
After the step:
Set Field[ConferenceDetails::EventID]
add a:
Committ Record/Request[ ]
Hmmm, what else could it be...
Let's look at the fields in your portal.
Enter layout mode and double click one of the fields such as Submission Date. In the dialog box that pops up, you should see ConferenceDetails in the drop down menu at the top and "::Submission Date" should be highlighted in the field list below it. If that's what you see, then I'm running out of possible explanations.
Report back what you find and if neccessary, I'll send you a private message with my email my address so that you can send me a copy of your file to look at.
I found it!
When I used FMP advanced's script debugger to step through the script, I noticed that instead of
Set Field[ConferenceDetails::EventID, $EventID]
You have
Set Field[ConferenceDetails::EventID]
You're missing that second parameter. Select that line in your script. Click the lower Specify... button and enter $EventId and the script will work as it should. Now that I've looked at your file, I'm also suggesting the following modification of the IF step to avoid accidentally creating more than one related conference details record:
If [Events::Conference = "Conference" AND Count(ConferenceDetails::EventID) = 0]
I can't give you precise details without knowing more about your tables and their relationships but here's a general outline:
Put the credit card info in a separate table. (you may already have this.)
Define a relationship that links to this table. Define it so that is valid if the checkbox or radiobutton field contains a specific value and is invalid if it does not. (Valid: there's a matching record(s). Invalid: There's no matching record(s).)
Now you can either place the credit card fields directly on a layout or display them in a portal. When you click on a radio button or check box to make the relationship valid, the info will appear, if you click the check box a second time or choose a different radio button value so that the relationship is invalid, the data will disappear from the layout.
If you'd like to get a more detailed description, describe your fields/tables in more detail, and I'll take a swing at it.