9 Replies Latest reply on Feb 19, 2014 9:15 AM by philmodjunk

    Display most recent portal record when in Table view

    arronman

      Title

      Display most recent portal record when in Table view

      Post

           I have a portal set up (thanks to Philmodjunk) that displays data fields from multiple visits to shops. Works perfectly well.

           The original records were all created in "Table" with a separate record for each shop visit (including a Date field). I then imported these records into a new table called "Shops" using a unique ShopID field which automatically lists one version of each shop. The portal displays all the visits to each shop and works fine in FORM VIEW as intended.

           I need to be able to scroll through the shops in Table view so that it is easy to see the latest visit to each shop.

           However! only the first visit (date) is displayed in Table View. I tried reversing the Date Order sort in the portal, but as I expected, it made no difference.

           It's really important to be able to display a Table view to quickly reference the most recent visits.

           The end users use FileMaker Go and don't have many options to make a mess (so far so good), but the number of shop visits is getting very large (well over 1000) so it's extremely tedious to scroll through on an iPad.

           Hope you can help.

           Cheers

        • 1. Re: Display most recent portal record when in Table view
          philmodjunk

               You can't use a portal in Table View as you have discovered, but you can set that same sort order in Manage | Database | Relationships such that the most recent related record is the first record and then you can include fields from that related table in your view.

               But you may find that using List view--where your portal will work, BTW, may produce a much more user friendly way for general users to work with your data.

          • 2. Re: Display most recent portal record when in Table view
            arronman

                 Thanks, had a quick look, but wasn't quite sure how to set it up. I then thought there might be neater ways to achieve the desired result.

                 I'd like to make clearer my intention for the user ...

                 Most of the shops need to be revisited on a regular basis: some 6 monthly, some annually, some every 2 years, but some never again.

                 We need a function that can search through the bunch of individual records for the shops (there's a separate record for each visit to each shop), determine the most recent visit and make a sub-set - in Table View , preferably, due to ease of scrolling through multiple records; but not essential. This needs to be an active, editable, sortable layout rather than a report.

                  

            • 3. Re: Display most recent portal record when in Table view
              philmodjunk

                   This needs to be an active, editable, sortable layout rather than a report.

                   sounds made to order for a list view layout. List view layouts support portals, buttons and other layout objects not visible in a table view. They also allow you to use layout text to label your columns instead of internal field names that can be cryptic.

                   To specify a sort order for a relationship, go to manage | database | relationships and find the relationship line between the layout's table occurrence and an occurrence of the related table. Double click that relationship line, then click the sort button for the related table and you'll get a dialog where you can specify a sort order for the relationship where you can sort your related records in descending order by a date, timestamp or auto-entered serial number field.

                   Then you can use the field tool to add fields from this related table to the body of your list view and the fields will show data from the most recent related record.

              • 4. Re: Display most recent portal record when in Table view
                arronman

                     OK, I guess I could create a list view where the relevant fields are contained on 1 line for quick scrolling, then include buttons or check boxes to refine or re-sort results.

                     I'll try that, thanks. Also hope I have success with the latest date.

                • 5. Re: Display most recent portal record when in Table view
                  arronman

                       Yes, created a new layout in List View, which nicely displays all the shops, but I haven't been able to work out how to get the latest date displayed, rather than the earliest.

                       Here is comment from you from an earlier query long ago (had to put database  mods on hold):

                       If you have a relationship:

                       Table1---<Table2

                       Then you can define a calculation in Table1 as Max ( Table2::DateField ) to get the most recent date of the set of records in Table2 that are related to Table1. You can also define a "maximum of" summary field in Table2 to get the same date--just place that summary field from table2 ond table1.

                       And summary fields also max dates for a found set of for sorted groups within the found set in a summary report.

                       So I created a date field in a new layout (data from Table named Shops) named LastVisit with the calculated value: Max(Shops::Date1). What's wrong? The result in the displayed LastVisit field is: "0/00/0" ????

                  • 6. Re: Display most recent portal record when in Table view
                    philmodjunk

                         Since there are no dates in your new Date field, You are taking the Max of an empty field.

                         There is no need for any calculation field you can sort the relationship to show the most recent related record as the first related record and then you can add fields from that table directly to your layout. This was spelled out earlier in this thread.

                    • 7. Re: Display most recent portal record when in Table view
                      arronman

                           I've been trying the Manage/Database/Relationship sorting without success. I now believe I know why.

                           To recap: I have a set of records which includes one record for every visit to every shop. With your p[ast help I created a separate Table, then used a Unique Field in order to copy in only one record for each shop. I then set up the portal. All good. Works nicely with the portal displaying all the visits to each shop.

                           My problem is that the original filtering grabbed the shop record with the earliest date as the only copy, hence my problem with latest dates.

                           So now it seems I need to be able to filter the original table using the unique ShopID field, but selecting only the shop record with the latest date. Is that possible/practical?

                      • 8. Re: Display most recent portal record when in Table view
                        arronman

                             There's a further complication ... The display of latest visits needs to be regularly updated. This means, I think, that I need a script that will hunt through the original table ("Table") and separate out one record for each shop, being the latest visit. This needs to be done frequently as any new records (still one per visit per shop), including any unique new shops are added to the original Table("Table").

                             Perhaps I should have started a new thread???

                        • 9. Re: Display most recent portal record when in Table view
                          philmodjunk

                               As I answered in your new thread, you need at least a relationship to the original table of shop visits so that you can use either a sorted relationship or a sorted portal to access the most recent visit. This data will not be present in your table of one record for each shop--nor should it be.