11 Replies Latest reply on Jul 15, 2009 8:09 AM by charliefoxtrot

    Two Tables, One Layout

    charliefoxtrot

      Title

      Two Tables, One Layout

      Post

      I'm using Filemaker to maintain a radio log, but am struggling with how to design a form around my tables. 

       

      Table #1 contains everything related to a specific event - the date and time it occurred, who was manning the radio, etc.

      Table #2 contains the chronological log, tied to table #1 by the event ID number. Each entry in this log has a timestamp and a summary of the communication that occurred at that time, and the station with which I was communicating. 

       

      What I'm looking to do is have a single form to enter all this information. The info for table #1 goes at the top, and I just fill out a table that expands down infinitely; each row of this table would create a record in table #2. 

       

      I know how this could be done using a scripting language such as Ruby on Rails or PHP. However, I'd really like to have a desktop application as a frontend. Is this something that would be possible in Filemaker? 

        • 1. Re: Two Tables, One Layout
          comment_1
             You are describing a portal.
          • 2. Re: Two Tables, One Layout
            charliefoxtrot
              

            Thanks! That got the data in there. However, all comms show up for all events - even events in which they don't belong. I think I need to change the relationship between the two tables, but I'm not sure exactly how to do that. 

             

            An event has many communications, a communication belongs to only one event. The only options use less than/greater than arrows or equal signs. I've tried using the following relationships (Table.field): Comms.eventID = Event.eventID, Comms.eventID >Event.eventID, Comms.eventID >= Event.eventID, and Comms.eventID X Event.eventID.

             

            Also, is there a way to allow a row in the portal to spread over multiple lines? I've got some really long fields in the comms table, but it only shows the first line. 

             

            Thanks for the quick response! 

            • 3. Re: Two Tables, One Layout
              comment_1
                

              You should have an EventID field in the Events table, defined as Number, with auto-entered serial number.

              You should also have an EventID field in the Comms table, defined as Number.

               

              The relationship needs to be:

               

              Events::EventID = Comms::EventID

               

               


              charliefoxtrot wrote:
              is there a way to allow a row in the portal to spread over multiple lines? I've got some really long fields in the comms table, but it only shows the first line.

              You can make a portal row taller - but it will affect ALL portal rows.

               


              • 4. Re: Two Tables, One Layout
                LaRetta_1
                  

                charliefoxtrot wrote:
                Also, is there a way to allow a row in the portal to spread over multiple lines? I've got some really long fields in the comms table, but it only shows the first line.

                You can also attach a tooltip to that field.  The calculation in the tooltip will simply be that field name.  Then will display all the contents when you mouseover it.  Also keep in mind that the field will expand if the User enters the field so it can be read that way (if they are allowed).


                • 5. Re: Two Tables, One Layout
                  charliefoxtrot
                    

                  Thanks for all the help! I have the relationship working now between the events and the communications.

                   

                  For anyone else that has this problem, what worked for me was to create the two automatically incrementing ID numbers--one ID per table--and link both of them using an = relationship. I wouldn't have thought to create the automatically incrementing ID numbers, since all events have their own ID based on the date and location (similar to how SSN's assigned). These ID's were removed from the layout, so they're only used by the database.

                   

                  (Event)EventID = (Comm)EventID

                  (Event)CommID = (Comm)CommID 

                   

                  Is there no way to make the portal automatically expand down as more text is entered? Right now, I can't print records because it only shows what's on the screen - it doesn't include all the records in the portal. It lets me add unlimited text, but it only displays a little bit at a time. 

                   

                  Thanks!  

                  • 6. Re: Two Tables, One Layout
                    comment_1
                      

                    charliefoxtrot wrote:

                    (Event)EventID = (Comm)EventID

                    (Event)CommID = (Comm)CommID 


                    That doesn't make much sense. Relating a parent to a child so:

                     

                    Parent:: ParentID = Child:: ParentID

                     

                    should be quite sufficient.

                     

                     


                    charliefoxtrot wrote:
                    Is there no way to make the portal automatically expand down as more text is entered? Right now, I can't print records because it only shows what's on the screen

                    Portals are not for printing. It's best to print from the child (Comms) table, with sub-summaries for Events. Then you can use the sliding option.

                     



                    • 7. Re: Two Tables, One Layout
                      charliefoxtrot
                        

                      It doesn't make any sense to me either, but when I don't have that field, it doesn't work. I created a working table with the double relationship, removed the commsID field from the database, and verified that the relationship was gone too. Now, none of the comms show up in the portal. I don't understand why it needs the double relationship, but apperantly it does. I'm using FileMaker 8, so it might just be an old bug or something. Regardless, it's working now. 

                       

                      Is there a better way to design this project, or is it simply not something that Filemaker can do? The problem with events is that they contain an indefinite number of comms, and comms can be any length, from a few words up to a paragraph. I'm looking to ultimately print these out in a nice report, with the event info at the top of the page, followed by the comms spread over the remainder of that page and any subsequent pages. Ideally, this is how the interface should look when I'm typing comms into the database in real time. 

                       

                      If this helps at all, currently this is accomplished by three text fields in the main table. One holds times, the other holds the call signs of the stations, and the third holds a summary of what was said. The problem with this is that if I go back to add more to the summary of communications, it knocks the times and call signs out of allignment. Since the data is recorded in real time, it gets frustrating when this happens, and often requires several hours post-event to clean up the columns and make sure the times and call signs are aligned with the summary. 

                       

                      • 8. Re: Two Tables, One Layout
                        comment_1
                          

                        charliefoxtrot wrote:

                        It doesn't make any sense to me either, but when I don't have that field, it doesn't work.


                        Could you explain what exactly doesn't work? This is the most basic data model there can be, and there's no reason why it shouldn't work for you the way it's supposed to do (even in v.8).

                         

                         


                        charliefoxtrot wrote:
                        I'm looking to ultimately print these out in a nice report, with the event info at the top of the page

                        As i said , base your report layout on the Comms table, with a sub-summary part (when sorted by EventID).

                         

                         


                        charliefoxtrot wrote:
                        If this helps at all, currently this is accomplished by three text fields in the main table. One holds times, the other holds the call signs of the stations, and the third holds a summary of what was said.

                        ...


                        I'm afraid I didn't get this part. Which one do you call the "main" table?




                        • 9. Re: Two Tables, One Layout
                          charliefoxtrot
                            

                          By "doesn't work," I mean the records don't show up at all, and it won't let me create new ones in the portal. As soon as I add the second id, everything works like it should. I don't really understand why this is, but it seems to need a double relationship. I know from my limited experiece with Ruby On Rails that this relationship can go both ways: an event has_many comms, and a comm belongs_to an event. 

                           

                          The problem with a sub summary is that there's no way to edit the fields inline. I'm looking to mirror the current interface as much as possible - these comms are actively used during the event, if I need to look back and see what was said a few minutes ago. The portal is cumbersome because it doesn't adjust to longer/smaller comms, but the sub summary doesn't work because I can't edit the fields. 

                          • 10. Re: Two Tables, One Layout
                            comment_1
                              

                            charliefoxtrot wrote:

                            By "doesn't work," I mean the records don't show up at all, and it won't let me create new ones in the portal. As soon as I add the second id, everything works like it should.


                            Then something must be wrong in the implementation.

                             

                             


                            charliefoxtrot wrote:

                            The problem with a sub summary is that there's no way to edit the fields inline.


                            Well, the idea is to use them for printing. There is no way to get a "flexible height" during onscreen viewing/editing. I would put a button in the portal row that shows more details of the selected row - either in a new window or next to to the portal (similar to a typical mail program).



                            • 11. Re: Two Tables, One Layout
                              charliefoxtrot
                                

                              If there's no way to expand the portal rows, I'm going to have to develop this in something besides FileMaker anyways. The most likely solution will be something in Objective-C/Cocoa or possible OpenOffice Base + MySQL. Thanks for all of your help with this. The community support for FileMaker is among the best I've ever seen outside the world of free software.