9 Replies Latest reply on Jan 14, 2011 1:01 PM by philmodjunk

    Help With List Field

    mgxdigital

      Title

      Help With List Field

      Post

      Hello,

      I have 2 databases: control and customers

      In customer database: there is a customer table and a sales rep table. These are related through sales rep ID.

      My control and customers databases are related through a Customer ID.

      Currently, i have a portal on a layout in my control database that shows  all of my sales reps. I made a count field in my customer table that counts the number of accounts created within a certain period of time.

      My portal on my control shows each salesperson and the number of accounts created during a date range. This part works great. I'm haivng trouble getting a list field to generate the names of the customer accounts that were created during the date range.

      I've been able to have the portal show every account for each sales rep, but not just the ones created during the time period.

      Screenshot.JPG

        • 1. Re: Help With List Field
          philmodjunk

          What version of FileMaker are you using?

          You mention getting this info in a list field, but then also mention trying to get this list in a portal. Which do you need? Or do you need both?

          With FileMaker 11, you can set up a portal filter, but this won't work for the list field. (A list field, as I define it would be a calculation field that uses the list function to list the contents of a field in the related table.)

          With FileMaker 10, you can't use the portal filter, but you can define a relationship that limits the matching records to a range of dates. This relationship will work for a list field, so you may want that approach even in FileMaker 11.

          Customer::CustomerID = Accounts::CustomerID AND
          Customer::Date1 < Accounts::AccountDate AND
          Customer::Date2 > Accounts::AccountDate

          Date1 and Date2 may be global fields--which can be useful in networked situtations.

          • 2. Re: Help With List Field
            mgxdigital

            Hi Phil!

            I'm using Filemaker 11.

            I need a list field since i'm putting the field in a portal.

            The portal shows each sales rep. So we have 6 sales rep (6 portal lines) and i'm wanting a list field on eacah portal line showing the related sales reps new accounts.

            I have Date1 and Date2 which are both global and my relationship between customers and sales reps is exactly what you listed above.

            Sales::SalesID = Customer::SalesID AND

            Sales::Date1 < Customer::Creation Date AND

            Sales::Date2 > Customer::Creation Date

            In my customer table, i have a count field that counts number of creation date.

            In my 6 line portal showing all of my sales reps, i have this count field which works. When i enter in a date into global Date 1 and Date 2 it will show me the count of the customers that had a creation date between those dates.

            Now, i'm looking for a list field to add to my portal that shows the company names (which would be in the customer table). 

            I want to show each sales rep via my sales portal, with the count (which is a related field through the relationship above). So if the count shows 2, then i want a list field to show me the 2 company names.

            I just can't get a the company name list to work.

            • 3. Re: Help With List Field
              philmodjunk

              So the layout is based on Sales and the portal on Customer?

              In my 6 line portal showing all of my sales reps,

              Are sales reps listed in the customer table or does this come from a related table?

              How, exactly, have you linked customers to accounts? (seems like this would be from salespersons to accounts, but I don't remember all the details of your database design.)

              In any case, since you are getting a correct count, it sounds like the list function should work for you:

              List ( RelatedTable::Field ) will list all values in Field for all related tables, separated by returns. Since you have a portal, you may need to see this list in a horizontal row.

              Subsitute ( List ( RelatedTable::Field ) ; ¶ ; ", " )

              can be used to make the list horizontal, with individual items separated by a comma.

              • 4. Re: Help With List Field
                mgxdigital

                Thanks Phil,

                I'm getting it to pull just one compnay name, i just can't get the list to function.

                I typed it just as you did and have it pulling from the same customer relationship that the count field pulls from (which works).

                I took a screenshot of the portal showing:

                Dan    1           Absolute Co

                House  4           Lowry

                Saharon  0    

                Alex  6          Wincraft 

                But i'm not sure how to put it to this thread.

                Any clues as to why it will give me just the first company name but not the rest?

                 

                • 5. Re: Help With List Field
                  philmodjunk

                  You can click Edit on your very first post and edit it there, but I don't think that screen shot will add anything. A screen shot of the portion of your relationship graph on which your portal is based might help.

                  I need a clear understanding of all the tables involved and how they are related in order to understand why it doesn't work.

                  • 6. Re: Help With List Field
                    mgxdigital

                    Uploaded the relationship screenshoot,.

                    On the left is the salespersonnel db and the right is the customers db.

                    They are related via the salesID.

                    The count field that i've been talking about and the list are both fields in the customers db.

                    The portal is pulling from the salespersonnel.

                    • 7. Re: Help With List Field
                      philmodjunk

                      I was looking for the entire graph found in Manage | Database | Relationships or at least that portaion that shows not only this relationship but where those account names are stored and how(if) they are linked to the portal's table occurrence.

                      This looks like a self join where global fields in Sales Reports SalesPersonnel link to records in a second table occurrence of the same table. I need to see the connection from Sales Reports SalesPersonnel New to what ever table stores the account names you want to list.

                      • 8. Re: Help With List Field
                        mgxdigital

                        Attached is a new screenshot of hte connection between salespersonnel to the customers.

                        The portal i made is coming from salespersonnel.

                        The fields i'm showing in my portal are:

                        sales person (which comes from salespersonnel)

                        count of new accounts (which is a count field of creation date in customers)

                        list of new accounts (which is a field that i'm trying to create. The company name is a field in customers that i'm trying to make the list field from).

                        • 9. Re: Help With List Field
                          philmodjunk

                          A screen shot of Manage | Database |Relationships would be very helpful here.

                          In your first shot you had the relationship linking Sales_Reports_SalesPersonnel to Sales_Reports_SalesPersonnelNew. Now you show me salespersonnel and Salespersonnel_Customers, telling me that the portal is based on SalesPersonnel.

                          Your layout is based on one and only one of these. It's listed in Show Records From in layout setup.... Your portal is based on one and only one of these. It's listed in Show Records From in portal setup...

                          That's what I was looking for to better understand how you could get a count, and not be able to use List to list the records that you are counting.

                          It looks like List ( Salespersonnel_Customers::Account ) defined to evaluate "from the context of Salespersonnel" would list all values in account from the related customer records, but I could be wrong here.