13 Replies Latest reply on Mar 15, 2014 3:38 PM by raphthepenguin

    Filter Portal to show only one row per record

    raphthepenguin

      I am working on a Reminder layout, the goal is to get reminded of who needs to be contacted soon.

      In this case there need to be replies to payments.

       

      I set the portal all up and everything works fine. The only thing is if one person made multiple payments that have not been replied to I have multiple portal rows for that one record.

      I'd like if only the oldest payment is shown!

       

      I guess thats filtering the portal, but I don't know how I can get something that results in a Bolean!

       

      Thanks

        • 1. Re: Filter Portal to show only one row per record
          siplus

          You can set the portal to show only one row and to be ordered by payment date from oldest to most recent one.

          • 2. Re: Filter Portal to show only one row per record
            raphthepenguin

            That way I will not be able to see payments from other people though!

            • 3. Re: Filter Portal to show only one row per record
              erolst

              raphthepenguin wrote:

              I guess thats filtering the portal, but I don't know how I can get something that results in a Bolean!

              Thanks

               

              Create a self-join by customerID and a calculation, say, isOldestPayment? like payments::Date = Min ( payments__selfJoin::date ), and filter on that.

              • 4. Re: Filter Portal to show only one row per record
                raphthepenguin

                Sorry you lost me here.

                 

                My relationship is

                 

                Reminder     =     CustomerPayment     =     Customer

                 

                Would I add the selfjoin to this or am I just making new Tableoccurances with Customer = Customer.

                Where would I create the Calculation?

                 

                Thanks

                • 5. Re: Filter Portal to show only one row per record
                  erolst

                  See attached file.

                  1 of 1 people found this helpful
                  • 6. Re: Filter Portal to show only one row per record
                    siplus

                    Sorry, I guess I answered to the title of your post instead of replying to its content.

                     

                    you should have :

                     

                    1) a Bill table  (Billl primary key: BPK)

                     

                    2)  a Payment table (Bill foreign key : BFK) with fields such as PaymentDate, AmountPaid

                     

                    3) a Clients table connected to the Bill table

                     

                    4) a relationship Bill_»_Payment connecting BPK to BFK

                     

                    5) a BillNotYetPaid calc field in (1), BP defined as ABS((BillTotal - Sum (Bill_»_Payment::AmountPaid)) > gTolerance with gTolerance set to, let's say, 1.

                     

                    6) a LastPayment calc field in (1), defined as Max(Bill_»_Payment::PaymentDate).

                     

                    7) a ContactCustomer calc field in (1) defined as (Get(CurrentDate) - LastPayment > 30) * BillNotYetPaid

                     

                    8) a ProblemIndex field in your client database (3) defined as Sum(Client_»_Bill::ContactCustomer)

                     

                    Now you go on a Clients layout which displays phone number and unpaid bills numbers, you find all records having a ProblemIndex > 0, sort by it Descending in order to have the most problematic clients first and... grab the phone.

                    • 7. Re: Filter Portal to show only one row per record
                      DavidJondreau

                      Or sort the relationship and just show the field (no portal).

                      • 8. Re: Filter Portal to show only one row per record
                        raphthepenguin

                        Wow, that is genious!

                         

                        Only one Problem:

                        For some reason my calculation field doesnt recalculate.

                         

                        So if I have an older payment and mark that as payed it doesnt recalculate to the next newer unmarked payment.

                         

                        I checked the storage options but they are set to recalculate if needed. Can I somehow trigger a recalculation?

                        • 9. Re: Filter Portal to show only one row per record
                          erolst

                          raphthepenguin wrote:

                          Wow, that is genious!

                          Not really … 

                           

                          The calc field re-calculates as needed (you can't index it anyway, since it references fields from other TOs), but you also need to update the portal display.

                          (And – not least – you must calculate the oldest date from a relationship that includes the payment status!)

                           

                          See attached.

                          • 10. Re: Filter Portal to show only one row per record
                            raphthepenguin

                            If I look at your solution it makes sense...

                            But I have trouble translating it into my solution.

                             

                            To fit into the endsolution I am working on different tables, Also I am not using a cortesian because I made a calc field that will say if the field has to be reminded or not///

                             

                            Maybe you can have a look where it hangs! I tried all sort of things.

                            • 11. Re: Filter Portal to show only one row per record
                              erolst


                              raphthepenguin wrote:

                              […] But I have trouble translating it into my solution. To fit into the endsolution I am working on different tables, […]

                               

                              Of course you are. But you should be able to adapt the two basic ideas to your setup.

                               

                              One thing that definitely prevents your posted file from working is that even though you created the self-join, your calc field has a missing field reference (which btw. is marked in demonic red and should be pretty easy to spot …).

                               

                              Correct that and see what happens. Then try to work the reminded status into the relationship, so you get the oldest date only of all non-reminded records for a client.

                               

                              One thing to do if something doesn't work: go back to the basics, e.g. show a list view of the CustomerPayments table, and check the calc field's value directly, then tweak the relationship and/or calculation field until you get the expected results. Only then start building the portal. The more layers of abstraction there are between your views and the raw data, the harder it is to see why something doesn't work (which it rarely does at the beginning).

                               

                              Road map:

                              1. Set up your relationship

                              2. Define your calc field; display a set of data (e.g. all payments of a client, some reminded, some not) and twaek 1. and 2. until if the calc gives you the correct results (so the oldest non-reminded payment record of a client has a 1, all others a 0)

                              3. Set up your portal, including the filter calculation, in which you can now use your functional calculation.

                              4. Eventually, make the portal usable (scripts, toggles, checkboxes, summaries, etc.)

                               

                              All with the benefit that you have my sample file to peek into …

                              1 of 1 people found this helpful
                              • 12. Re: Filter Portal to show only one row per record
                                raphthepenguin

                                Thanks, I found the problem!
                                Because I had the tickbox not resulting in a Bolean, but just in a letter it was hard to get the relationship established...

                                 

                                For now I just created another field cal field that is 0 when its paid and 1 when not and I established the relationship with the reminder key that is 1 if its not payed.

                                So with that the calculation for the filter only looks for field that need to be paid and are the oldest.

                                 

                                Now I just have some extra steps that I will look at next week, fix and delete them!


                                Thanks for bring me on the right path!

                                • 13. Re: Filter Portal to show only one row per record
                                  raphthepenguin

                                  This is a good solution, thank you!

                                  But in my case I didn't look for people who haven't paid most bills, just for who's oldest nonpaid bill wasn't dealt with. Thank you though! I will definately use that in future solutions!