Summing a text list in a portal

[je duce]

I'm attempting to sum a text value for a specific field in each record appearing in a portal. The aggregate function "SUM" works wonderfully for numeric values but no luck for text. I can add values from two text fields by using the concatenate operator "&". Doing so from record to record is not clear to me. Is there a way to add values from a previous record to the present record to form a running total (or list as I wish to do). My ultimate goal is to this within a portal. Searching these forums has yielded nothing regarding text values in this way. Maybe I should post this after the holiday, but like all things that naw at the old gray matter it just wont go away till its resolved. Thank you in advance.

edit- summing is a better description , need to paste multiple texted items from each portal record into one list.

[Vaughan]

The term you're looking for is "concatenate": numbers are summated, text is conactenated.

Text from related records can be concatenated using the ValueListItems function, or by using one of the Trio plug-ins.

The ValueListItems function returns the concatenated result as a paragraph-dlimited list, sorted alphabetically ascending, with all duplicate entries removed. It is quite a powerful function since it pulls a lot of information out of the related records; consequently it can be relatively slow -- like 1 second per record slow.

[je duce]

Vaughan,

Thanks for your response. Being new to FileMaker I'm still learning to speak FM. I was just finishing the solution to my dilemma when I received your response. I discovered the ListValueItems in conjunction with FilterValues will get my desired result. The maximum length of value list generated is only 6-12 words so there is no response time issues. I continually encounter solutions on this forum referencing the Trio plugins. I do need to study that. Where would you suggest I go to learn more about the use and description of these Trio plugins you refer to.

[Fenton]

That would be: http://www.troi.com, and the Text plug-in. But, as you say, ValueListItems can do quite a lot. Another great plug-in is the Troi File plug-in, but it is not often needed in Mac-only situations, as AppleScript can do most of what it does.

If you're just beginning with FileMaker, and have already figured out how to get the value list items then filter them with FilterValues, you're to be congratulated. There is also a possibility to use a relationship to filter a value list, if that applies.

[je duce]

Fenton, Thanks for the post on Troi plugins. Will check that out later. Oh, thanks for the kudos also. Started on FM 7.0 at the beginning of the year. So I have a long long ways to go. As to using relationships with filter and value list, I think I need a break. I may be back to you on that one.

[Fenton]

The reason I mentioned using a relationship to filter the value list is because you said you wanted to get the text items in a field in a portal. A portal is by definition filtered by a relationship. By "filtered" I mean the plain use, not "FilterValues" which is a FileMaker function.

FilterValues should not be needed here, unless you wanted to remove some values from the resulting list.

If the value list is defined to use the same relationship as the portal, "starting from" the same table occurrence as the layout where the portal is (to establish the base point of reference), you just need to specify the field you want, and it will get all the values for that field for that portal's relationship.

This is what Vaughn said (in a more concise manner). You must be using a relationship to filter the value list; otherwise how could you be getting values from a related table?

[Vaughan]

"The maximum length of value list generated is only 6-12 words so there is no response time issues.

The performance hit comes from the number of records in the related file, not so much the length of the text in the value list.

I routinely work with databases with 10,000 to 100,000 records. So the ValueListItems function has to sift through 100,000 records looking for matches and concatenating the results. It can take ~1 second for each record to update.

This is with FMP 6 (well, 5.5 actually) hosted on FMS 5.5. It may be different (better or worse) with FMP 7. Probably better.

[je duce]

Thanks for the clarification. As I said, I have a ways to go before I can speak FM. It fact your right on the use of filterValues. I'm creating two list to identify conflicting line items within a portal. In the DB each line item has a unique set of conflicting items which must not appear in the same list for a given record. Thus each record in the portal has a field for the partNumber of line item itself and a field denoting the potential conflicting partNumbers. By creating a list for each and then use the FilterValues function I can see if any items match. If it does I then easily display the partNumber or partNumbers which are conflicting. If I could only display which specific portal row the conflicting partNumbers occur in I would have my ultimate solution. Any thoughts?