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.
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.
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.
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
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?
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.
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.
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.
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).
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.