13 Replies Latest reply on Apr 23, 2014 5:52 AM by JamesKnecht

    Having Trouble Displaying Data From Portal

    JamesKnecht

      Title

      Having Trouble Displaying Data From Portal

      Post

           Very, very new to Filemaker pro.  I am rewriting our old software that was written in dBase IV.  So far I have imported my customer file table & my ledger file table & they are related by the account number field.  So far so good except for one issue that I just can't figure out.  I wish to display the balance from the ledger on the customer file.  The current balance is in the last record of the table for that account number.  I have set up a portal displaying the balance field but it is displaying the balance from the first record... not the last.  I assume I need a script so I set one up saying Go To Portal Row [Last}.  I went to Layout Setup & established a trigger for the script OnRecordLoad.  Still get date from the first record of the ledger.

            

           I have never written a script before.

        • 1. Re: Having Trouble Displaying Data From Portal
          mgores

               You could reverse the sort order on the portal so that it shows the last record first.

          • 2. Re: Having Trouble Displaying Data From Portal
            philmodjunk

                 You don't need a script for this and you may not need a portal for it, but let's stay with the portal for now:

                 Is there a field in Ledger that increases with each new record added to it? A serial number field or maybe a date or timestamp field?

                 If so, enter portal setup... and specify that the portal be sorted in descending order on this serial number or other field. Then a single row portal will show data from the very last field.

                 Note: An account balance can also be a summary field that computes a balance or running balance from all the records for that account. It's possible to get this account balance without needing the portal if you define the correct summary field in ledger to compute that account balance.

            • 3. Re: Having Trouble Displaying Data From Portal
              JamesKnecht
                                  

                                       You could reverse the sort order on the portal so that it shows the last record first.

                             

                   Right now that table in the portal is not sorted.  How do you reverse the sort order in that case?

              • 4. Re: Having Trouble Displaying Data From Portal
                philmodjunk

                     To repeat:

                     

                          Is there a field in Ledger that increases with each new record added to it? A serial number field or maybe a date or timestamp field?

                     

                          If so, enter portal setup... and specify that the portal be sorted in descending order on this serial number or other field. Then a single row portal will show data from the very last field.

                • 5. Re: Having Trouble Displaying Data From Portal
                  JamesKnecht
                       

                            Is there a field in Ledger that increases with each new record added to it? A serial number field or maybe a date or timestamp field?

                       

                            If so, enter portal setup... and specify that the portal be sorted in descending order on this serial number or other field. Then a single row portal will show data from the very last field.

                       Yes, there is a date field.  However when I do as you suggest, it displays the first record on that day... not the last.  So it moved me way down in the table but not to the bottom.  This would work if only one post per day but with multiple posts daily, it doesn't appear to.

                  • 6. Re: Having Trouble Displaying Data From Portal
                    JamesKnecht

                         So what does Go To Portal Row [Last] do?

                    • 7. Re: Having Trouble Displaying Data From Portal
                      philmodjunk

                           Read my post again:

                           

                                Is there a field in Ledger that increases with each new record added to it?

                           If there are multiple records added per day then a date field is not a field that increases with each new record added to it. A Timestamp field that enters date and time, however, might be used. Also, if, there is a serial number field that increments with each new record--and such can easily be added and replace field contents can update the field for all current records, you could use that serial number field.

                           But Here's an alternative: Define a calculation field in the client table and use the Last function to display the value of the last related record in the Ledger table.

                      • 8. Re: Having Trouble Displaying Data From Portal
                        JamesKnecht
                             


                                  But Here's an alternative: Define a calculation field in the client table and use the Last function to display the value of the last related record in the Ledger table

                             Sounds rather complicated, especially for a beginner.  I'll see if I can figure out  how to do this tomorrow.

                              

                        • 9. Re: Having Trouble Displaying Data From Portal
                          mgores

                               If you double click the portal from layout view a dialog will pop up allowing you to set sort order, filter, # of rows to show, etc.  Click on the sort button and you can select your options there,  Of course that won't help unless you have a sequential field like a timestamp or a serial number field to do the sort on.

                               Phil's "last record" solution sounds a lot more elegant and is more simple than it sounds.  Just create a new text field in you client table, once you created it -select options. from that dialog select auto enter calculation option and for the calculation use the last function on the field in your ledger table.  Set it to do not store, recalculate as needed.

                          • 10. Re: Having Trouble Displaying Data From Portal
                            philmodjunk

                                 Yes, but it has to be a field of type calculation, not text. An auto-entered calculation won't automatically update when data in a related table updates--such as by adding anew record.

                            • 11. Re: Having Trouble Displaying Data From Portal
                              mgores

                                   (Smacks myself in the head) ... Thanks Phil

                              • 12. Re: Having Trouble Displaying Data From Portal
                                JamesKnecht
                                     


                                          Phil's "last record" solution sounds a lot more elegant and is more simple than it sounds.  Just create a new text field in you client table, once you created it -select options. from that dialog select auto enter calculation option and for the calculation use the last function on the field in your ledger table.  Set it to do not store, recalculate as needed.

                                     Sorry to be so stupid, but I don't understand.  When I Go to field options for this new calculation field, the specify calculation window comes up.  At the top, in the evaluate field, I have my customer file.  In the current table I have the ledger file selected.  If I click on the BALANCE field, what operator do I use?  Then, I understand I select the Last (repeatingField) function.

                                • 13. Re: Having Trouble Displaying Data From Portal
                                  JamesKnecht

                                       Figured it out.  I assumed the window would walk me thru the command.  Assumed I would pick something from the left, add an operator, then select a function.  When that didn't work, I tried some manual commands & this worked for me.

                                        

                                       Last(Ledger File::BALANCE)       Where Ledger file is my linked AR table & BALANCE is the field I need the last record in.

                                        

                                       Don't know if I did this the proper way... but it works.

                                        

                                       THANKS FOR THE HELP!!!!