8 Replies Latest reply on Jan 16, 2017 9:23 AM by hottonem

    Modifying Custom Field to Display Most Recent Information?

    hottonem

      Hello!

       

      I am very, very new at FileMaker, so please bear with me. Our system is all set up and I'm just trying to maintain it, clean it up a bit, and slowly learning.

       

      In list view, we have two custom fields set up for the type of plan purchased and the date of the transaction. These fields pull up the data from the first receipt, rather than the most recent. We're like to alter the fields to display the most recent plan information and date of the transaction on the receipts from the customer profiles.

       

      I've been playing around a bit trying to figure it out, but as I'm quite inexperienced with FileMaker, I'm wary to change much. Was I on the right track going to File > Manage > Database, heading to Relationships and attempting to change the Receipts Data for the Plan Type to Descending beside the Sort records check box?

       

      Thank you for any and all help!

        • 1. Re: Modifying Custom Field to Display Most Recent Information?
          erolst

          It is a bit difficult to answer this without knowing what exactly your tables and relationships are, or what you mean by custom fields (calculation fields?).

           

          If you want to display data from the last related record, then yes, sort the relationship descendingly, and simply display fields via that relationship (without a portal, you'll always reference the first related record as per the sort order). No calculation fields necessary.

          • 2. Re: Modifying Custom Field to Display Most Recent Information?
            hottonem

            Sorry I'm not quite up with the terms yet! One is a text field, while the other is a date field. They draw the information from receipts on customer accounts, and I just want them to show the most recent rather than the first entries. Sorting them in table view doesn't do anything because the fields themselves still aren't displaying the information I need them to.

             

            I went into layout mode to try and edit the field options...

             

            Capture.PNG

            Is this generally on the right track? I've played around a little but it doesn't seem to be changing anything in table view, at least. Again, sorry I'm so lost. So new at this!

            • 3. Re: Modifying Custom Field to Display Most Recent Information?
              philmodjunk

              You need to show us what tables and relationships you have.

              • 4. Re: Modifying Custom Field to Display Most Recent Information?
                hottonem

                Relationships.PNG

                Meal Plan Type Relationship.PNG

                The two fields I want to show the most recent information from the customer receipts are listed as student_TRANSACTIONS::Meal Plan Types and student_TRANSACTIONS::Date.

                 

                I thought sorting the data under Receipts Data in descending order would have done it, but it didn't appear to change anything.

                 

                Descending Order.PNG

                 

                Thanks for your help!

                • 5. Re: Modifying Custom Field to Display Most Recent Information?
                  philmodjunk

                  "last visited record" would mean the last record edited by the user in the same table. I don't think that's what you want here. The relationship dialog shows a broken relationship as there's no match field specified for the Products | Looked Up table occurrence. (A table occurrence is what we call a box in the relationships graph and a "table" is not quite the same thing as a "table occurrence".) In fact, I don't see any fields at all in that table occurrence.

                   

                  Let's back up a step and gather a few more details. WHen you enter layout mode and click the pencil icon to the right of the layout name, you open a dialog called layout setup. For the layout where you need to see this happen, what name appears in "Show Records From" in the Layout Setup dialog?

                   

                  And in which table did you define this text and date field? Are you trying to copy data to get what is "most recent" at the moment or do you want to show this most recent data in a way that will always update to show the most recent information? And in what table is this "most recent" information?

                  • 6. Re: Modifying Custom Field to Display Most Recent Information?
                    hottonem

                    I guess what I want the most recent information, updated in the student_TRANSACTIONS table when I enter a receipt, to show up in the students table when I'm performing a search. I'm not sure how to get layout view to work when I'm performing a search, it just goes back to the customer list.

                    Customer Receipt.PNG

                    Customer List Layout.PNG

                    Such as below, I'm filtering the customers based on their status, which is active. Though their profile is active, it's showing the first plan they purchased and the date which it was purchased.

                    Find Details.png

                    These plans are no longer available, so it looks like the database is super out of date. But when I go into their customer profiles, they've purchased plans this year--I'd like the search to reflect this updated information instead. The information is entered in the student_TRANSACTIONS table while the customer list when I'm searching is just the STUDENTS table. I'm guessing at this point I'm going to have to play around with the relationships chart and make some connections here...

                     

                    This database has been worked on by many people in the past couple years, so I'm just attempting to make it a little easier to keep updated and clean. 

                    • 7. Re: Modifying Custom Field to Display Most Recent Information?
                      philmodjunk

                      when I'm performing a search. I'm not sure how to get layout view to work when I'm performing a search, it just goes back to the customer list.

                       

                      If you perform a find on a layout based on "Students" you are finding records from that table and the resulting found set will be made up of records from that table. If you want to find records from a different table you either perform your find on a layout based on that different table or you use a different method for accessing/displaying the desired data. And the found set pulled up on this other layout will have no effect on what records you see on a different layout if that other layout is based on a different table occurrence.

                       

                      If you want to show this information on your second layout, named "Students", do the following:

                       

                      1) Go to manage | Database | Relationships

                      2) Double click the line (or the = sign) linking Student_Transactions

                      3) Click the sort check box on the Student_Transactions side of the dialog (or click Specify...) that opens the sort dialog. Clear any selected fields and then select Date, specify Descending Order

                      4) Now click Ok until you are fully out of Manage | Database and add any fields from Student_Transactions that you want to show on that layout directly to the body of the layout. In browse mode, these fields should show data from the related record with the most recent date in the date field.

                       

                      Options:

                      You may find that you need the current relationship sort order and thus changing it creates issues for other parts of your solution. If so you can:

                       

                      a) Add another occurrence of Student_Transactions to your relationships, linked by the same fields. You can then specify your sort order in the relationship to this occurrence and add fields from this occurrence.

                       

                      Or

                       

                      b) Keep your relationship graph unchanged with the original sort order in this relationship. Add a one row portal to Student_Transactions to your students layout and specify that the portal be sorted in Descending order by Date. You can make the borders of the portal transparent to keep these fields looking just like any other fields on your layout and not like fields in a portal if you wish.

                       

                      .

                      • 8. Re: Modifying Custom Field to Display Most Recent Information?
                        hottonem

                        PERFECT.

                         

                        You are wonderful, sir, and I thank you very much! It did exactly what I needed it to, and this'll make everything so much easier.

                         

                        Thank you!!