1 2 Previous Next 26 Replies Latest reply on Aug 20, 2013 2:03 PM by philmodjunk

    Filtering a

    KurtSnow

      Title

      Filtering a

      Post

           My questions concerns two joined tables in one database. The first table is companies. The second table is contacts. On the first table I need to create a report that lists all the companies with the sales rep and their phone number, which resides in the contacts table. In the contacts table I have a field called "contact:type" which includes a drop down list of variables like sales, accounting, shipping, etc. I do not know how to create the report to just include the contact:type of sales.

        • 1. Re: Filtering a
          philmodjunk

               I will assume that you have this relationship:

               Companies------<Contacts

               Companies::__pkCompanyID = Contacts::_fkCompanyID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Though your names may differ from mine.

               If so, then base your report layout on Contacts instead of Companies. You can include fields from companies (such as a company name field) in a sub summary layout part (see part setup...) "when sorted by" _fkCompanyID and put fields from Contacts in the body.

               On this layout, you can perform a find for all contacts where Contact Type = Sales and then sort the resulting found set of records by _fkCompanyID and you'll get a list of companies and their sales reps group beneath each company name.

          • 2. Re: Filtering a
            KurtSnow

                 OK. That works. Thank you again.

            • 3. Re: Filtering a
              KurtSnow

                   I have run into similar problem that effects the orders database which is a separate databse but is joined to the company database. I crated an order, and now I need to address it to the sales person. I need to somehow filter all of the different contact types to just include the sales contact.

              • 4. Re: Filtering a
                philmodjunk

                     Are you trying to select a sales person from a value list of just sales persons in order to enter their contactID into a field in invoices?

                     That can be done with a conditional value list:

                     There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

                     The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                     Forum Tutorial: Custom Value List?

                     Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                     Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                     Hierarchical Conditional Value lists: Conditional Value List Question

                     Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                • 5. Re: Filtering a
                  KurtSnow

                       Thank you for the links and demo file. This does not seem to solve my problem, however. This is how my orders database works. I select the company, in this case it is a media company (radio, TV, cable, Internet). So, I enter an order for KABC in Los Angeles. Orders is linked to Media Company so oall of the info about KABDC appears. But, I am selecting the company, not a contact. Once all the orders to the stations are entered (it can be dozens of stations connected to a single buy). Then I need to print a number of reports. I need to print a report that lists the stations sales contact, another report prints the accounting contact, another report prints the person who receives the commercial (called traffic), etc. In my old database, prior to converting to Filemaker, I had a field to match all those contacts in the media company database -- a sales field, traffic field, acconting field, etc. along with their contact info -- phone, fax, email, etc. The problem is that there can be more than one person in sales, traffic, accounting, plus other contact types. It got very unwieldy. So, it made sense to me to create a separate contact database that is linked to the media company database. In the contact database, to dientfiy the contacts I have a category (sales, traffic, accounting) and a postion (Accounting MAnager, Accounts Receivable, etc.) To see all the contacts I have a portal  on the media company's layout. So far, so good.


                       The problem comes when I get to the order database. When I click on each respective report -- sales, accounting, traffic, I need to see the respective contact and their info. So, somehow I need to filter the report so that includes only the "Sales" category with the position of "Order Desk," etc. The conditional value list that you reference above depends upon me selecting the contact. Since I am creating multiple reports based on seleting the company, I do not think this will work. Any other thoughts, or do I have to go back to my old fashioned method of creating all the contact types in the same media company database?

                  • 6. Re: Filtering a
                    philmodjunk
                         

                              The conditional value list that you reference above depends upon me selecting the contact.

                         Not necessarily, by selecting the company, you can get a conditional value list of just contacts for that company.

                         But with the additional info you have now posted, that doesn't appear to be what you are doing here. Let me see if I have this correct. Even though a company record links to many contacts, in this context, we only want to link to one contact, the one linked to company in the sales category and the position: "Order Desk"? And this criteria will always link to just one contact for a given company? or might it link to several contacts and you'll have to pick from that list of several?

                    • 7. Re: Filtering a
                      KurtSnow

                           It would be unique ... there would only be one contact per company with the position "Order Desk," or "Accounts Receiveable," etc. Although (and here is another wrinkle), sometimes the station does not use their in-house sales person but rather contracts with an outside rep firm. To handle that contingency I use an "If/Then" calculated field, i.e., If Order Desk is blank then use the contact from the Rep Firm.

                      • 8. Re: Filtering a
                        philmodjunk

                             There are several possible approaches to link to that one specific contact, given the company.

                             1) Put the fields from contacts in a filtered one row portal. Use a portal filter to specify that the category is sales and the position is "order desk".

                             2) Define a pair of calculation fields, constSales and constOrderDesk. Set them to always have these values by specifying "Sales" and "order desk" as their calculation expressions. Then use these in addition to the company ID to match to a new table occurrence of contacts.

                             3) Use ExecuteSQL to reference the desired contact info.

                        • 9. Re: Filtering a
                          KurtSnow

                               I am looking to explore #2, but I have a few questions. I have at least three reports in the same database--sales, accounting, traffic. How would each report filter the results if I link a new sales contact calculation field to a new table occurence. I think that would work if I was dealing with just the one variable of a sales contact, but I am not sure if it works with three.

                               Also, how would I use three fields to match to a new table occurence?

                          • 10. Re: Filtering a
                            philmodjunk

                                 Using option #2, you'd need a separate "constant" match field for each linking to a different occurrence of the contacts table. This is, in fact, the main draw back to option #2. Option #1 does not have this limitation, nor does option 3.

                            • 11. Re: Filtering a
                              KurtSnow

                                   I placed a portal on my sales report. The report is based on the Orders database. The portal is based on Contacts. As a test, I filtered the portal records by:

                                   Contacts::type="Accounting" & Contacts::position="Accounts Receivable"

                                   But it returns the sales contact. What am I doing wrong?

                              • 12. Re: Filtering a
                                philmodjunk

                                     Your are using the concatenation operator: & instead of the logical operator: And

                                     Make it:

                                     Contacts::type="Accounting" and Contacts::position="Accounts Receivable"

                                • 13. Re: Filtering a
                                  KurtSnow

                                       Thank you so much for your help and patience. Unfortuantely, I got a weird result. The right contact position shows up, but the wrong name. I think I may not have my joins set up properly. Let me explain: The Contacts to Media Company is a many-to-many relationship (a company can have more than one sales person, and (in media) a sales rep can sell more than one station). I created the contacts database, a second table occurrence of Media Company and tied them together via a MediaCoContactJoin table. Then I link the the first (and main) table occurence of Media Company to the Join table. I have a portal on the Media Company database that displays the records. Everything is fine when there is only one contact, but as soon as I add another contact to the company things begin to get buggy, in this case the email addresses in the email field disappear. Here is the structure of my tables and joins:

                                        

                                       Contacts-----< Media Company Contacts Join >----- Media Company

                                       Media Company 2-----< Media Company Contacts Join

                                        

                                       And here are the fields names used in the joins that correspond with the tables above

                                       __pkContactID-----< _fkContactID >-----__pkMediaCompanyID

                                       __pkMediaCompanyID-----< _fkMediaCompanyID

                                  • 14. Re: Filtering a
                                    philmodjunk

                                         There seems to be a relationshp problem here unless this is just an error in how you described it:

                                         __pkContactID-----< _fkContactID >-----__pkMediaCompanyID

                                         Where you have one field in blue, there should be two fields, one that links to contacts and one that links to media company. It should not be the same field used for each--which will keep this from being a many to many relationshp.

                                         It should be this:

                                         Contacts::__pkContactID =  Media Company Contacts Join::_fkContactID
                                         Media Company::__pkMediaCompanyID = Media Company Contacts Join::_fkMediaCompanyID

                                    1 2 Previous Next