11 Replies Latest reply on Jan 25, 2011 12:02 PM by Sorbsbuster

    Displaying Multiple Entries From A Different Table

    AndrewGilman

      Title

      Displaying Multiple Entries From A Different Table

      Post

      Hello,

      I'm new to this forum and filemaker as a whole.  I was hoping for some advice, so I'll describe my database so far.  It's used to organize patient data.  There are two relevant tables here: Patient & Scan (I'm tracking MRI scan data).  Every patient has one unique identifier used throughout the database, their medical record number (MRN).  Each patient will have only one MRN.  Each scan is treated like an individual event (for the sake of other tables to store results and just general structure of our servers), so each scan has it's own unique Scan ID.  So while 99% of all patients will have one Scan ID as well, they may have 2 or more.  The Patient and Scan tables are linked by the MRN.

      My question derives from a need to see the Scan ID while looking at the patient table.  It's pretty tedious to switch back and forth between the patient and scan tables so I can find the particular scan ID for a patient.  This is necessary because all of our data is coded via these scan IDs.  So I'm hoping that I can get all of the scan IDs that a patient has to display automatically on the patient table.  I tried doing this with both the portal and field tools on the patient layout, but this will only display the first instance of a scan ID.  For instance, if a patient has 3 scan IDs, only the first will be displayed under the portal on the patient layout, negating the functionality of such a portal.  I tried setting the setup to include repetitions 1 through 10 (I'm a newbie so I don't know exactly what this does) but that just showed the first instance with nothing else.  Any suggestions?

      Thanks!

        • 1. Re: Displaying Multiple Entries From A Different Table
          FentonJones

          Well, it should work, if you put the related Scan::ScanID field from the (same) related Scan table inside a portal on the Patient layout, using a relationship based on:

          Patient::MRN = Scan::MRN

          If the portal is showing more than one row, you should see the multiple scans, one in each row. So you are very close, but there is something you're doing wrong. Are you seeing only 1 field/row/ScanID? (then likely it's not quite in the portal, or the portal has been set to 1 row only, or the relationship is actually based on ScanID, because you've incorrectly put ScanID in the Patients table also). Or are you seeing multiple rows with the same ScanID? (likely the field has not been specified via the correct relationship).

          We can only guess without seeing the file. A Clone of the file (no data) would do, if you could upload it to a web site and put a link here.

          • 2. Re: Displaying Multiple Entries From A Different Table
            Sorbsbuster

            You don't need to use repetitions.

            Are you sure you have you linked the two tables using the the MRN?

            If you are in the Patient Table, you draw a portal using the Patient Table::Scan Table relationship (by MRN) and set the portal to be, say, 4 rows deep, it should most definitely display up to 4 scans (you can add a scroll bar if you want), exactly the way you want.

            Are you sure the data fields you have added in the portal are within the portal boundary?  If they are even a pixel outside it they will not actually be in the portal and will only show the first matching record, as you've seen.  If you used the complete 'portal creation wizard' it will look after you and draw the portal fields in the right place, even though they will not be the right size for you, probably.

            Do not despair: Filemaker will work exactly the way you want.

            • 3. Re: Displaying Multiple Entries From A Different Table
              Sorbsbuster

              Simultaneous posting with Fenton...

              • 4. Re: Displaying Multiple Entries From A Different Table
                AndrewGilman

                Wow thank you both for your detailed responses - that's one thing I've noticed as vastly different between filemaker and access and their communities.

                Anyway, I messed around with it some more and did "insert > portal" rather than drawing one with the portal tool.  This time a typical box popped up (it looks identical to other field boxes) as did a shaded gray region that didn't show up last time.  When this shaded gray region was placed under the box, all the Scan IDs showed up.  When the two items were separated, only the first Scan ID showed up, as was the original issue.

                Well that pretty much resolves it, thank you both.  I have other questions about filemaker that I'll probably ask soon, but are a little more detailed.

                • 5. Re: Displaying Multiple Entries From A Different Table
                  AndrewGilman

                  Actually is there a way to have the multiple entries show up horizontally rather than vertically?  Thanks.

                  • 6. Re: Displaying Multiple Entries From A Different Table
                    FentonJones

                    Yes, it is a little odd, but fairly simple. Each portal has a # of rows to show, in its dialog. There is also an option for Starting Row [  ]

                    So you set the portal you have to show 1 row, and start at: 1

                    Select the Portal and Field; Duplicate the portal (and field)

                    Reset the duplicate portal to still show 1 row, but start at: 2

                    Repeat as many times as you want, just increment the starting row.

                    It is actually still 1 portal (same relationship), so the field remains the same; it just shows the value from each row (record). But now independent, you can put them anywhere.

                    P.S. You can (of course) do this with blocks of 10, 20, whatever # of rows, if you increment the starting #.

                    • 7. Re: Displaying Multiple Entries From A Different Table
                      AndrewGilman

                      Thanks again, one more question:

                      So this portal system seems to work for Form View, but not Table View.  Is there a way to display this kind of information in Table View (as I can't modify it's layout, so don't know how to add portals)?

                      Thanks Again!

                      • 8. Re: Displaying Multiple Entries From A Different Table
                        FentonJones

                        You can easily create a calculation field in Patients which showed their ScanIDs as single horizontal list; using the List() function with some text substitution. List() by default produces return-separated values; you'd likely want something like commas.

                        Substitute ( List (Scan::ScanID); ¶; ", ")

                        But Table view has a serious restriction, which is that you cannot put active buttons in its rows. I consider them to be a kind of utility for viewing data where you want to move stuff around, or sort in various ways quickly. But a bit annoying otherwise.

                        • 9. Re: Displaying Multiple Entries From A Different Table
                          Sorbsbuster

                          Are you using Table View simply as an alternative to one-record-at-a-time form view?  You should look at creating a layout with all the good bits of Table View that you like, but set it to be List View, and get the best of both worlds.  I agree with Fenton over the usefulness of Table View - can't remember the last time I used it.  IMHO it's only a sop for those folks who need to see everything as an Excel table or "This. Does. Not. Com. Pute."  Though quickly flicking column orders and sort orders and hiding columns is pretty neat.

                          • 10. Re: Displaying Multiple Entries From A Different Table
                            AndrewGilman

                            Haha I love that robot phrase, except I add an "Err. Ore" in front haha.

                            I had thought about creating a layout that mimicked table layout, but I don't know if the work justifies the gains.  Here is what I use table layout for:

                            The database is the final frontier for our data - we have such a large backlog that it's used as a "this data is finalized" storage method.  This means that I'll have, say, 100 patients that I have data on who haven't been put into the database.  I use table view to quickly get a feeling of who I've already put into the system.  I prefer to see more than one record at a time for this, obviously, and the only limitation to table view (for me at this point in time) is that it won't display the multiple Scan IDs.  This doesn't seem to be much of an issue, when the alternative would be to go up to the layout bar, find the right layout (and start to collect more and more layouts which might make naming conventions not as clear cut for whoever replaces me when I leave), and then see all the patients, then switch back to the other layout.  I work on a Mac, so tedious processes like that are usually solved with keyboard shortcuts, and I just don't really see a need to make a new layout for that sole purpose (especially when it's rare that a patient will have more than one Scan ID).

                            Thanks again for all your help, I deeply appreciate it.

                            • 11. Re: Displaying Multiple Entries From A Different Table
                              Sorbsbuster

                              "I use table view to quickly get a feeling of who I've..." Now, that sounds to me like a Find script.  If you know what you're looking for when you glance down the list ("No date here, less than 2 of those there, longer than 3 months ago some place else, higher than a 3.5 on this value...") then stop all that looking and scrolling: distil your job knowledge into a script, or a couple of scripts.  Define what you're looking for, hit the button, and wham: that's the list.  No ifs or buts.  If your first 'pass' of the list is to find all those people who haven't got a ...whatever, then write a script for that and stick a button for it on the screen.  And if the second pass is to double-check all those good folk who have a score higher than... well, you get my drift.

                              (And if you don't know what you're looking for when you scroll down the list: well, we'll just not go there.  <Irish Attempt At Humour>)