Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Problem with data tunneling


jmek66
 Share

Recommended Posts

I need help getting data to show up in multiple places within a database. I am trying to use the data tunneling techniques in the Filemaker Missing Manual from O'Reilly, but am more than willing to try a different approach.

 

The situation:

I have been building a database to track a medium-large festival, with roughly 60 events and 570 people (presenters, coordinators, volunteers, etc.)

 

I have one table listing all the People which includes not only their general contact information but also the name of the events where they will perform and the name of the specific performance (eg., Paul Simon is the person, Opening Night is the event, and "Blowing in the Wind" is the performance.) Another table lists all the Events, including a portal showing the name of the people and performances for the event (drawing from the Performances table described below). Since there is a many-to-many relationship between People and Events, I have an intermediate Performances table which only shows the person's name, the event name, and the performance name. The Performances table is linked to the People table via the person's full name and to the Events table via the event's name.

 

The problem:

I need to have data entered into one table also show up in the other tables. So if an event coordinator starts typing in all the people/performances via the Events table, that information also shows up in the People table. On the other hand, if a performer coordinator types events/performances via the People table, I want it to show up in the Events table. And information entered into the Performances table should show up in both People and Events. The connections work some times but not always and I can't see any consistency to the missed connections.

 

Can anyone tell me the correct way to set up this database and/or places to look to see why the current connections aren't working?

 

Thanks in advance for your help.

 

jeannine

Link to comment
Share on other sites

Hi Jeannine

 

The first thing I strongly caution you about is where you say "The Performances table is linked to the People table via the person's full name and to the Events table via the event's name." This is fraught with danger - for example, change an event's name slightly from "Opening Night" to "Grand Opening Night" and all the performances disappear! Follow the advice of using serial numbers to uniquely identify records for relationships. You also said "The connections work some times but not always and I can't see any consistency to the missed connections" and this is indicative of the problems when using people's names or event names as keys.

 

Now for your problem. I have attached a sample database showing how relationships and portals can be configured to do what you want. When you enter a new perfoirmance record in the portal on the People layout, it will also appear in the portal of the relevant event record.

 

I have used data tunnelling in the portals. For example, on the People layout, you create a new performance record by selecting a PersonID from the pop-up list. The person's name is shown (using merge fields) from the table two steps away.

 

Have a look at the example and see how you go.

Link to comment
Share on other sites

Yep I had the same problem with making sure people enter information correctly and I also find the best way to achieve this is to have drop down (or which ever you fancy) value lists where you know people have to enter information.

I really believe and trust data integrity and find the only way to acheive this is make sure you don't allow too much "user" input into fields that are (especially) related to other fields. You will save yourself a lot of hassle by doing this.

Sometimes when I know that a user may wish to enter something that is not in the value list, I allow the entry of "other" and they can type in there. Then I do a search on the "other" items entered and you can then add them to the value list or re-enter them using the correct value.

Trust me you will save yourself alot of grief by dissallowing many users to enter their own information.

Maybe you could meet with them and decide on the starting list for each value and go from there?

Hope this helps.

Link to comment
Share on other sites

Hello qmrs and David,

 

Thanks for all your help. I have taken your separate pieces of advice (use unchangeable IDs, not changeable names to create relationships; restrict user input with drop-down lists) and they have solved a number of problems in my database. But now I have a new one. When I enter new data in the Performances table it does not always show up in the portal on the Events table. However, it does create a new blank line in the Events portal where the new data should go. Is there something similar to a field relookup for portals? Or can you think of any other reason for the blank lines?

 

Cheers,

jeannine

Link to comment
Share on other sites

Hmmm, I wouldn't be entering performances that way - I would enter new performances via a portal on the Events layout or on the People layout. That way you don't have to specify one or the other.

 

What you may be seeing is a portal refresh issue. Try going to the next record and then back to force the portal to refresh.

Link to comment
Share on other sites

Hi David,

 

Actually, I would rather enter performances via the portals on the Events and People layouts, but I haven't been able to get that to work. Truth to tell, entering from the Performances (now called "Readings") isn't working very well either.

 

My biggest problem (at the moment) is making it so that users can enter new performances/readings via the portals in the Events and People layouts and have the new information flow through to all three layouts (Event Detail, People Detail, Readings-data entry). Would you be willing to look at the attached mini version of the database (only about 20 records rather than the 700 in the full version) and let me know what I'm doing wrong?

 

Thanks in advance,

jeannine

 

 

Hmmm, I wouldn't be entering performances that way - I would enter new performances via a portal on the Events layout or on the People layout. That way you don't have to specify one or the other.

 

What you may be seeing is a portal refresh issue. Try going to the next record and then back to force the portal to refresh.

Link to comment
Share on other sites

I see what you are doing - you are displaying data from two tables away. That will work but it will not allow you to CREATE the linking table (Reading) records.

 

Solution: On both the Event detail and the People detail layouts, you need a portal that displays the Reading table and then tunnel the data from more than one table away. You have to create Reading record data first so you can then attach Event (or People) data to it.

 

For example, from the People detail, if I want to add a Reading, I enter a Featured Work name. This creates a Reading record linked to the person. I can also enter the status of the request.

 

I can then enter an event ID (for the Reading record, not the Events table) and then that will display the tunnelled event data such as event name, date and time. Tunnelled data fields are set to be non-enterable in browse mode so that someone doesn't inadvertently change the event data.

 

Have a look at your file modified to do this on the People detail layout. You would make similar modifications to do this from the Events detail layout. See how you go.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use