Designed a database for 8.5, now I find out it's going to be used in 8. I have a calculation field that determines an overall status of these related records by using List() function (depressingly native to only 8.5). Any ideas how to revamp this field definition?

Field Definition:

Let( [ $BinStatusList = List( OrBin_OrderNumber::Status_Bin ) ];

Case(

IsEmpty( $BinStatusList ) or PatternCount( $BinStatusList; "Needs Duplication" ) = PatternCount( $BinStatusList; "¶" ) + 1; .........blablablablabla....restofcasefunction..........

)

)

There's not a way to have a Get( RelatedRecordCount ) or something, is there? With that I suppose I could make a custom function with a self-calling Get( PortalRowNumber ) if I knew how many portal rows there were.....

Then again, I love to complicate things. Gotta be a simpler way.

If you happen to have fm advanced lying around...

ListFieldContents( Field ; RelatedCountField ; result ; n )

Let( [

n = n + 1 ;

numRecords = Count(RelatedCountField) ;

result= If( n > 1 ; result & ¶ ) & GetNthRecord( Field ; n )

];

Case( n >= numRecords ; result ; ListFieldContents( Field ; RelatedCountField ; result ; n ))

)

Usage:

List( YourRelatedField ; YourRelatedPK ; "" ; "" )

Returns:

My

Name

Is

Earl

And

Isn't

Really

Alex

Next Record Is Empty

But The CF Doesn't Care

Cause It's Counting

A primary Key.

Edited March 12, 200718 yr by Guest
Changed "my" to "your"

These examples by John Mark Osborne should help you.

Comparison of different approaches to the List function

Portal Info in a list

mr_vodka --- the "Portal Info in a List" downloaded an empty folder. but the other link you posted worked fine. that Comparison is a great example, thanks!

Genx --- I do happen to have a Developer copy sitting around so I ended up using ListFieldContents() function. thanks! that was a good quick fix.

Both downloads worked for me.

Lee

Hm, weird. I've tried it on my G4 and on my Intel and I'm still just getting an empty folder. Odd...

Is there is setting that you have to change on your mac to display files with no application associated with them? You probably dont have FM6 and the files are .fp5 files. Once located, you can just convert them to .fp7 by dragging and dropping into the FM8.

Edited February 9, 200718 yr by Guest
TYPO

Nope. Macs show all files regardless of whether you have an application associated with them. (I do have FM6 by the way.)

I ended up going to their website and navigating to see if I could find it. A couple other .zips did the same thing.... I've tried a few browsers, too; no avail. I'll try on another computer in my office tomorrow. Dunno!

I'm guessing that ListFieldContents is an FMP 8.5 Advanced function, because it doesn't exist in FMP 8.0 Advanced.

Or am I missing something here?

What is "MyRelatedPK"?

The primary key of YOUR related table.

Hi Dave, your missing something

Its a custom function, i.e. one you create yourself using the advanced version of FM.

Go to File > Define > Custom Functions.

Create a New Function

Call the function:ListFieldContents

Add these 4 as the variables:

( Field ; RelatedCountField ; result ; n )

This is the main portion:

Let( [

n = n + 1 ;

numRecords = Count(RelatedCountField) ;

result= If( n > 1 ; result & ¶ ) & GetNthRecord( Field ; n )

];

Case( n >= numRecords ; result ; ListFieldContents( Field ; RelatedCountField ; result ; n ))

)

Just to clarify, it doesn't have to be a primary key (your related table might not have one) it just has to be a field in your related table that is never empty... primary keys just always exhibit that characterstic