Text summary of portal contents

[Charles Henebry]

I'd like to get a text summary of the contents of a field from related records in another table.

FM does this all the time internally when you ask it to create a dynamic value list from related records. But I can't figure out how to set up a calculation or summary that outputs a text list of one field from another table, with entries separated by line returns. All the summary functions have numerical output.

I could use a portal if all I wanted to do was to view the results, but I'd like to able to manipulate the results using text functions.

[QuinTech]

Hi, Charles. Check the manual for the "ValueListItems" function. I think this may point you towards what you're looking for.

HTH,

Jerry

[Charles Henebry]

I tried this, but found that the value list doesn't update reliably. When you open a record, it seems like FM recalculates all calculation fields so that they're all up-to-date. But with "ValueListItems" function, FM thinks it's giving you up-to-date info if it returns the current state of the value list in question, even though that value list may well be out-of-date. The only way to get the value list to update is to call it from a pull-down menu or the like.

By contrast, portals are recalculated on-the-fly when a record is opened. But what I'd like is a summary of a column from a portal as a text field.

Perhaps this just isn't readily doable on FM. But if so, it's odd that it isn't since (as I noted) FM seems to do this internally when it creates valuelists.

[QuinTech]

Under Storage Options for the calc field, have you checked off "Do not store calculation results?"

[dave8952]

Please excuse my newbie-ness, I'm new to Filemaker(and database-ing) - I just bought FMpro7 and am dusting off the brain cells in creating a database.

My problem is similar to Charles', but slightly different.

Here's my problem:

In Table A, I've got a text field (let's call it, "List of Dates". I want this text field to return a list, in text format, (for instance, "3/15, 3/16, 3/20" etc) of a date field in a related table (through a portal) which has many related records. The list would also contain commas ( , ) between the dates.

What I've already done, is create a text field in the related table, that converts the contents of the single date field (in the same record) to text. I'm then trying to return a list of all those "date-converted-to-text" fields into ONE text field in the other table.

There use to be a command (in my old database program) called LIST, but I can't find that in FMpro7.

Any help would be appreciated. The more I think about this, the more I begin to forget my own name.

Thanks,

Dave :confused

[QuinTech]

Dave, i would recommend you also use the ValueListItems function, but replace the pilcrow ("") with a comma and a space. So create a value list with values from the field TextDate, and then your calc field would look something like:

Substitute (

....ValueListItems ( "TheDatabase.fp7" , "TheValueList" ) ,

...."" ,

....", "

)

HTH,

Jerry

[QuinTech]

Post deleted by QuinTech

[Lee Smith]

Hi Jerry,

Get a Mac. LOL

I think Steve changed this when he removed the

[dave8952]

Thanks for the help, Jerry. I'll give it a shot. I'm assuming that the ValueList that I create from the 'DateToText' field will only return those values that are linked to the specific record. Is that correct?

Or will it return all date values present in that field, for all the records?

thanks again,

Dave

[QuinTech]

Or will it return all date values present in that field, for all the records?

The latter.

[dave8952]

Maybe I should re-word my issue a little better. I'm finding it hard to explain it clearly, but here goes.

In Table-A, there's a text field (TextA) that has many linked records from Table-B. In the Table-B records is a DateToText field (DateB). The dates listed in this field are different than those that are related to other records in Table-A. I only want to list the dates(DateB) that are related to that one record in Table-A. So that, as I scroll through the records in Table-A, the text field will return a list of only the related TextToDate fields.

Did I explain it better?

Thanks, dave

[QuinTech]

I see. Then define your value list to "Use values from first field: [RelationshipName]" and select the DateToText field. Then that value list will contain only the dates from Table B that are related to the selected record in Table A.

[dave8952]

Cool. I'll give that a try.

Thanks.

[dave8952]

Jerry,

That worked great!!! Thanks.

Now...........my DateToText formula

GetAsText ( MonthName ( AdRunDatePrint::RunDate ) & " " & Day ( AdRunDatePrint::RunDate ))

is giving me the full name of the month. Is there a way I can just get the short version?

Thanks - you're the greatest!

[QuinTech]

I would use nested Substitute functions, as in:

Substitute (

   Substitute ( 

      GetAsText ( MonthName ( AdRunDatePrint::RunDate ) & " " & Day ( AdRunDatePrint::RunDate )), 

      "January" , 

      "Jan"

   ) , 

   "February" , 

   "Feb" 

)

But others may have better ideas. I would recommend you search the forums for something like +date +"short name" (or variations) and, if that turns up nothing, post the question in the Define Fields forum.

[QuinTech]

Charles: I have just realized i am a willing participant in a post hijack! Sorry about that. Let us know if you have any follow up questions on my last post to you:

Under Storage Options for the calc field, have you checked off "Do not store calculation results?"

Dave: This is why it is almost always better to start a new post rather than piggybacking on someone else's! No biggie, and i'm really more at fault than you because i've been around here longer and should know better. Piggybacking should be limited to only questions that are directly applicable to the original poster's question, IMHO.

[Charles Henebry]

Actually, I was glad you all continued the conversation. Dave's question was closely related to mine, and your answers to his questions actually deepened my understanding of my problem. Sometimes the value of synergy outweighs the danger of hijacking, IMHO.

And thanks, Jerry, for all your help.

Best,

Chuck

[QuinTech]

Good, then everyone's happy.

Dave, i realized that there is a much better solution than the Substitute i proposed:

Left ( GetAsText ( MonthName ( AdRunDatePrint::RunDate ) & " " & Day ( AdRunDatePrint::RunDate )) , 3 )

[comment]

Perhaps

Left ( MonthName ( AdRunDatePrint::RunDate ) ; 3 ) & " " & Day ( AdRunDatePrint::RunDate )

[QuinTech]

Oops! Thanks, comment!

[dave8952]

Hey thanks, guys. And again, I'm sorry I unwittingly hijacked your post, Charles. This was my first post, and like always, a learning experience. I'll 'keep my posts to myself' (so to speak) in the future.

Thanks for all the suggestions. I can tell from the responses, that in this function stuff, there's more than one way to skin a cat. (Apologies to all cat-lovers)

Dave