10 Replies Latest reply on Aug 26, 2013 10:25 AM by KurtSnow

    Portal filtering

    KurtSnow

      Title

      Portal filtering

      Post

           This question deals with 3 tables that are joined together. The first is Media Companies, the second is Offices and the third is Orders. Once I create a record in Orders, I need to print out a report with the correct office address (there is more than one address assigned to a station as they often require payments to be sent to corporate headquarters, rather than the local office; or, they have a PO Box that I must use, instead of the local, physical address.) The portal works just fine on my report if I simply filter by office type (local or mailing [for Post Office boxes], etc.) This is the problem: Not every company has both a local, HQ and/or mailing address. Most just have a local address. If I set the filter to HQ, then only those station with an HQ address show up. I tried an If function, but I cannot get it to work -- I assume because I am not pointing to two different fields, but the same fields. Any suggestions?

        • 1. Re: Portal filtering
          philmodjunk

               I'd define a field and set it up with a single checkbox: Billing Address. If the record's address is the billing address, click the check box. If it is not, leave it unchecked. Now you have a single value in a single field that you can either match to in a relationship or filter for in the portal filter.

          • 2. Re: Portal filtering
            KurtSnow

                 I set up a checkbox field. At first, I tried using multiple values as I have more than one use for the field (I have reports I need to generate that need to list either the accounting address, mailing address, or the local address). That did not work. So, I tried selecting just one value: mailing. I still cannot get it to work. How do I designated the value for the filter? I used this in the filter calculation: Office::checkboxOfficeType = "Mailing"

            • 3. Re: Portal filtering
              philmodjunk

                   Yes but I suggested not using multiple values for one field. Having additional values in the same field can keep this method from working or it requires a more complex filter expression in order to get the correct value in the portal.

                   If Office is the name of the portal's table occurrence and if checkboxOfficeType is of type text, your portal filter expression is correct for fields that contain only a single value. If you can confirm the two "ifs" that I just listed, then I suspect that due to your experiments with multiple value checkboxes, there is additional data beyond just the text "mailing" in the field. You can inspect the actual contents of a check box formatted field by placing a copy of the field next to the checkbox formatted original and changing that copy back to edit box format. You may need to click or tab into such a field to see all the values that are present as each selected value from a check box format enters a value that is separated from other values by a return character. And removing a checkbox value from a value list, does not remove the value from any fields formatted  with check boxes using that value list.

              • 4. Re: Portal filtering
                KurtSnow

                     Ah ... that is eactly what happened :-( Thanks again.

                • 5. Re: Portal filtering
                  KurtSnow

                       One more challenge...one of my reports I need to list the contact with the field of "type" set to "Sales". However, if the company has an outside rep firm, then I need to list the rep contact instead. This was easy before I set up a separate contact table. I just used and If/then calculation field. Now, however, I am not sure how to do this as I have more than one contact for each station with a contact value list that includes such values as "sales" "accounting" etc.

                  • 6. Re: Portal filtering
                    philmodjunk

                         Are there contacts related to the same company where one has "sales" as the "type" and another is the outside rep?

                         Or is it a problem that one outside rep can represent multiple companies in your database?

                    • 7. Re: Portal filtering
                      KurtSnow

                           Yes, there can be both an inside salesman and an outside rep firm associated with the same company. I have a "use the rep firm" checkbox in the Media Company database. The attached screenshot shows my Media Company database layout which shows the portal for the company contacts and a separate area for rep firm info. A reminder ... the issue in question concerns the Orders database which links to Media Company.

                      • 8. Re: Portal filtering
                        KurtSnow

                             Oops. Wrong image. That is of the relationships. Here is the layout.

                        • 9. Re: Portal filtering
                          philmodjunk

                               And on what table occurrence is that layout based?

                               I would set up a single field in Media company with an fk field that links to the pk field for contacts. Call it "billing Contact ID" or some such and format it with a value list or other data entry method so that you can select a specific contact as that company's billing contact. That gives you a simple, direct reference to one and only one contact to use for billing purposes for every media company.

                          • 10. Re: Portal filtering
                            KurtSnow

                                 Yes, that worked. Thank you again.