Custom Function To get the list of IDs from the current found set

[MetaSys Software]

Hi All,

 

I have written one Custom Function To get the list of Product IDs from the current found set.

 

Custom Function Name: GetProductIDs

Parameter name : FountCount

 

GetProductIDs ( FountCount ) =

---------------------------------------------------------------------------------------------------------------------------------------------------------------

If ( FountCount  >  0 ;
GetProductIDs( FountCount - 1 )  & If ( FountCount = 1 ; ""; "¶") & GetNthRecord ( Products::Product_ID ; FountCount) ;
""
)

---------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Note: "Products::Product_ID" this is my product id field from my "Products" table. You can use any field which you want from your current found set.

 

In the Calculation Field: just write following to get the result of custom function

 

GetProductIDs ( Get ( FoundCount ) )

 

 

Regards,

Trupti

 

MetaSys Software Pvt. Ltd.

India

[bruceR]

See this function and others mentioned in comments for better solutions:

 

http://www.briandunning.com/cf/1323

[MetaSys Software]

Hi BruceR,

 

Your's solution is better since it is a generic function. we can just specify the field for which we want the list of data and we can get the result accordingly. and My solution is only specific to "product id" field.

 

Thanks for sharing the solution.

 

- Trupti

[MartieH]

thank you very much for this! I had been using a calculation involving substitutions and value lists in order to generate a list of related records - this is much much better!!! I am in the process of changing over to this in all my dbs - THANK YOU!

Martie

[HALBURN]

Can this be done natively in FMP13 or does this still require a custom function?

[Kris M]

Here is a scriptwize, superfast, way to accomplish the goal...

http://www.modularfilemaker.org/module/hyperlist/

[Wim Decorte]

Can this be done natively in FMP13 or does this still require a custom function?

 

And custom functions ARE native.  Just like scripts.  

 

Your use of "still" sort of implies that you don't want to use custom functions if you can avoid it.   You're cutting yourself out of a lot of functionality if you avoid them.

[comment]

Can this be done natively in FMP13 or does this still require a custom function?

 

A custom function is a native solution. But yes, in v.13 you have the new ListOf type of summary field.

[HALBURN]

And custom functions ARE native.  Just like scripts.  

 

Your use of "still" sort of implies that you don't want to use custom functions if you can avoid it.   You're cutting yourself out of a lot of functionality if you avoid them.

 

Sorry, what I meant to ask was... Is there a new standard function that would make the custom function referenced by BruceR obsolete? It sounds like perhaps the answer is yes, the 'ListOf' type summary field.

[Wim Decorte]

Check out the link to HyperList that Kris posted earlier in the thread.  It compares different ways of doing and shows which is faster based on the size of the found set.

[Mark Scott]

Sorry, what I meant to ask was... Is there a new standard function that would make the custom function referenced by BruceR obsolete? It sounds like perhaps the answer is yes, the 'ListOf' type summary field.

 

Yes, in the FM 13 era, ListOf is probably the fastest way to gather IDs from a found set (at least the fastest that doesn't mess with the user's clipboard). As Kris and Wim suggested, do check out Todd's Hyperlist module; I believe he updated it after 13 came out, to use "ListOf" when available. In my testing, "ListOf" is several times faster than the pre-13 version of Hyperlist, although that (pre-13) approach is well worth studying for it's brilliant bit of optimized coding. 

 

BTW, depending on what purpose you're gathering IDs for (e.g., Master-Detail layout or other virtual list-based technique), you may want to pay attention to Daniel Wood's comments (in the Hyperlist discussion) regarding virtualizing FM's internal Get(RecordID) (unstored) rather than a stored ID, at least if your solution is hosted. Gathering a stored value requires that the client machine has all the record data, whereas, gathering an unstored FM Record ID can happen almost instantaneously, long before client has all the record data. It can make a huge difference.

 

Mark

[Kris M]

Sorry, what I meant to ask was... Is there a new standard function that would make the custom function referenced by BruceR obsolete? It sounds like perhaps the answer is yes, the 'ListOf' type summary field.

Obsolete? No.

Not optimal? Perhaps.

Test various aproaches in your application and compare the results.

Data doesnt lie.

[Wim Decorte]

Gathering a stored value requires that the client machine has all the record data, whereas, gathering an unstored FM Record ID can happen almost instantaneously, long before client has all the record data. It can make a huge difference.

 

 

There are two more options that do not require the user to have all the data:

- using ExecuteSQL()  --> this is automatically executed on the server as long as the user does not have an open record in the target table

- using PSoS

 

Both require of course that you can reproduce the logic that produces the found set and that may carry too much overhead.  But they are certainly worth considering.