List function order

[sfpx]

Image 1

Image2

Image3

 

WorkerList field is a calculated field for which the calculation is : List(Worker::Name)

 

All occurrences are sorted by the created time stamp (descending) as in the example in Image3

 

I would like the workerlist field to list in the order of workevents

so in the example above (Mary,Eric,Ann,Luke)

 

The only way I got it to work like that was by adding another sort for the Worker occurrence as illustrated below

the bct field is a calculated field that gets WorkEvent::ct

 

 

 

 

 

 

 

Image4

 

 

It works but it's sorting on a calculated field and it's certainly not ideal.

 

Is there an easier way other than using SQL ?

 

[eos]

Create a calc field in WorkEvents that holds Worker name and List() that field.

[sfpx]

Create a calc field in WorkEvents that holds Worker name and List() that field.

 

But then I will get duplicate values if many workevents are made by the same worker.

 

The calculated field has to return all the UNIQUE worker names by order of their last work event.

 

Of course I could use the calculation you mentioned and then use a  custom function to remove the duplicates (that's actually what I'm currently testing) but it's certainly not the ideal solution.

[Lee Smith]

 Why didn’t you just post a copy of your file Instead of all of those screenshots?

 

 A copy or a markup of the file speeds things up a lot and makes it easier for us to follow what  you are asking.

[eos]

Yes, you're right; must have briefly taken leaves of my senses …

[sfpx]

There you go.

File attached.

ListOrderTest.fmp12.zip

[eos]

Add a field id_contract as auto-enter Contract::id to the WorkEvent table; create a self-join relationship where

 

WorkEvent::id_worker = WorkEvent_self::id_worker 

WorkEvent::id_contract = WorkEvent_self::id_contract 

 

In WorkEvent, define a calc field as

 

Case ( WorkEvent::ct = Max ( WorkEvent_self::ct ) ; Worker::name )

 

List() that field in Contract.

[LaRetta]

Another idea which might give you what you need ...

 

Filtered Portal

This is a technique which makes portal contents unique, that we learned from Comment a few years back here:

 

 http://fmforums.com/forum/topic/71906-getting-more-out-of-filtered-portals-3unique-values/?p=375983

 

The only sort is on the WorkEvent relationship from relation to Work descending on ct (creation time).  The relationship is already relationally filtered to only WorkEvents for that Contract so the question is ... what would you anticipate is the maximum number of related WorkEvents for a Contract?  If less than 1,000, I should think it is less expensive than multiple unstored calculations and extra table occurrence. And of course deployment will make a difference so I suggest you try all options to see which works best for you.  YMMV.

 

File attached for review.

 

 

 

 

ListOrderMOD.fmp12.zip

[LaRetta]

And I just thought ... a good ExecuteSQL() can provide the list of WorkerIDs of only their last WorkEvent.  I'm not that great at SQL but using DISTINCT and LOWEST (I think), LOL.  Maybe I'll try that later or someone who rocks at ExecuteSQL() can put it together for you.  The concept would be fill a global field in Contract using script with the WorkEventIDs (plural) as multiline and join to another table occurrence of WorkEvents.

 

edited:  modified bold to plural.

Oliver, you're better at ExecuteSQL() I think!

Edited November 27, 2014 by LaRetta

[David Jondreau]

Your solution of sorting by a calculated field from WorkEvent won't work unless WorkEvent and Workers is 1 to 1 too. Worker will grab the first related WorkEvent ct regardless of whether it's the one you want.

 

So...Subcontract --> Work --> WorkEvent are all 1 to 1 relationships? Why so many levels of 1 to 1? Or you might be having trouble understanding how multiple levels of relationships work?

 

I think the UniqueValues() custom function on a List() of WorkEvents::WorkerName is your best bet.

[eos]

Oliver, you're better at ExecuteSQL() I think!

 

Do you mean: better at ExecuteSQL() than, say, you – or better at ExecuteSQL() than at FileMaker …?   

 

Anyway … another approach would be to combine the de-duping portal technique with a “List of” summary field for a WorkEvent (yes, I know …) calculation field of the worker name.

So...Subcontract --> Work --> WorkEvent are all 1 to 1 relationships? Why so many levels of 1 to 1? Or you might be having trouble understanding how multiple levels of relationships work?

 

What makes you think they're 1-to-1? I've read them as 1-to-many, with WorkEvent being a sort of TimeSheet table.

[LaRetta]

I think the UniqueValues() custom function on a List() of WorkEvents::WorkerName is your best bet.

 

Just to clarify, the modified file I presented does not use a custom function nor does the one in the link I provided (by Comment) where the filtered calculations are identical (except different entities).  Did I miss something in either thread where a custom function was discussed or are you pointing to a different custom function?  :-)

One-to-many is how I perceived it as well (according to the forks in the graph), even though Contracts should be changed to unique so it becomes a single line instead of a fork.

[David Jondreau]

The OP's second post in the thread references the custom function option.

 

I may be confused about how multiple hops through a graph work...I need to do a little testing.

[sfpx]

Another idea which might give you what you need ...

 

Filtered Portal

This is a technique which makes portal contents unique, that we learned from Comment a few years back here:

 

 http://fmforums.com/forum/topic/71906-getting-more-out-of-filtered-portals-3unique-values/?p=375983

 

The only sort is on the WorkEvent relationship from relation to Work descending on ct (creation time).  The relationship is already relationally filtered to only WorkEvents for that Contract so the question is ... what would you anticipate is the maximum number of related WorkEvents for a Contract?  If less than 1,000, I should think it is less expensive than multiple unstored calculations and extra table occurrence. And of course deployment will make a difference so I suggest you try all options to see which works best for you.  YMMV.

 

File attached for review.

 

 

It's pretty cool but I need a list (that will be used in a script) and not a display method.

Sorry I was not specific enough in my OP.

And I just thought ... a good ExecuteSQL() can provide the list of WorkerIDs of only their last WorkEvent.  I'm not that great at SQL but using DISTINCT and LOWEST (I think), LOL.  Maybe I'll try that later or someone who rocks at ExecuteSQL() can put it together for you.  The concept would be fill a global field in Contract using script with the WorkEventIDs (plural) as multiline and join to another table occurrence of WorkEvents.

 

edited:  modified bold to plural.

Oliver, you're better at ExecuteSQL() I think!

 

Of course SQL will work but I'm trying to avoid using ExecuteSQl as much as possible as it tends to be slower than using FM relations.

[eos]

It's pretty cool but I need a list (that will be used in a script) and not a display method.

Sorry I was not specific enough in my OP.

 

Well, then why don't you create a list using the portal (as was suggested in #11)?

 

Of course SQL will work.

 

Care to share the code? I would love to learn how to do that.

 

EDIT: removed obsolete attachment.

[sfpx]

Your solution of sorting by a calculated field from WorkEvent won't work unless WorkEvent and Workers is 1 to 1 too. Worker will grab the first related WorkEvent ct regardless of whether it's the one you want.

 

So...Subcontract --> Work --> WorkEvent are all 1 to 1 relationships? Why so many levels of 1 to 1? Or you might be having trouble understanding how multiple levels of relationships work?

 

I think the UniqueValues() custom function on a List() of WorkEvents::WorkerName is your best bet.

 

Not quite sure why you assumed those were 1 to 1 relations. They are not. 

The only relation that is not 1-to-many is the last one between Workevents and Worker.

Note that the table names are completely fictional. It was just to illustrate my problem

 

I'm currently using the UniqueValues approach...

Basically I added one calc field in the workevents table that gets the worker name.

So the list would be RemoveDuplicates (list(workevents::workername))

 

Removeduplicates was found there http://www.briandunning.com/cf/492

Maybe there is a better function but this one works fine so far

[LaRetta]

It's pretty cool but I need a list (that will be used in a script) and not a display method.

 

But you showed a portal originally!  Not for display?  Well then, I would not create a calculation at all.  Why burden your tables with two additional needless calculations, the first with the WorkerName (unnecessary) and the second de-dupping (which is a heavy hitter).  I would GTRR to the WorkEvents and loop the records using Fast Summaries, storing the result in a script variable as a multiline to be used in your script.

 

If you would like an example, I can provide one later in the day - I'm leaving to go eat turkey.  

[sfpx]

But you showed a portal originally!  Not for display?  Well then, I would not create a calculation at all.  Why burden your tables with two additional needless calculations, the first with the WorkerName (unnecessary) and the second de-dupping (which is a heavy hitter).  I would GTRR to the WorkEvents and loop the records using Fast Summaries, storing the result in a script variable as a multiline to be used in your script.

 

If you would like an example, I can provide one later in the day - I'm leaving to go eat turkey.  

The portal was just to compare the result of the list field to what it should be Sorry I was not clear on what I needed.

BTW I moved the de-dupping from the calculated field to a script step .

 

Anyway I'm really intrigued by your idea.

I would really appreciate the example you offered. 

Thanks and Happy Thanksgiving !

[eos]

 I would GTRR to the WorkEvents and loop the records using Fast Summaries,

 

Unless I'm overlooking something, FastSummary is of no use here.

 

You cannot sort by name, since that sort order is what we want to investigate in the first place; and if you sort by date/timestamp, you'd still have to investigate all members in each group, which means you need to investigate all set members, which means that grouping doesn't really help.

 

The portal was just to compare the result of the list field to what it should be Sorry I was not clear on what I needed.

 

Better luck next time … Here's another example with several techniques.

ListOrderTest_eosMOD_v2.fmp12.zip

[sfpx]

Unless I'm overlooking something, FastSummary is of no use here.

 

You cannot sort by name, since that sort order is what we want to investigate in the first place; and if you sort by date/timestamp, you'd still have to investigate all members in each group, which means you need to investigate all set members, which means that grouping doesn't really help.

 

 

Better luck next time … Here's another example with several techniques.

 

Thanks eos.

I'll test the methods and compare to what I currently use and will pick the most efficient.

Not a fan of the overhead of an added portal but if that gets faster results I'm all for it.

[LaRetta]

I am not a fan of complexity either although I haven't yet seen the file Eos produced.  You are correct, sfpx, that a relationship is better approach but only if it does not load the schema with extra clutter and complexity.  I believe the best method, since you only need this for another script and not for display,  is to use a script.

 

Method #1 semi-fast summaries

 

No, Fast Summaries will not work in this case (I was just thinking out loud visualizing loop where we can short-circuit and not loop all records).  This requires no calculations, custom functions, portals AND you can remove the sort from all relationships (which can be expensive).

 

It will not be quite as fast as Method #2 because it loops the WorkEvents records but it stops as soon as it has identified all of the Workers for a Contract. It also means you can ditch the pn (person name) calculation from WorkEvents since it loops records and can gather that information through the Workers relationship.

 

Method #2 sort relationship

We don't even need to leave the Contracts layout at all and we can instead loop a variable.  WorkEvents must be sorted descending on ct (creation time). This does use the pn calculation already existing in WorkEvents.  All sorts from the other relationships can be removed as can all the other calculations, portal etc.

UPDATE:  In now reviewing all files and methods, the semi-fast summaries (method #1) seems the leanest.

I present the result in a comma string but of course $result is a regular list as you requested.

ListOrderMOD2.fmp12.zip

[sfpx]

I am not a fan of complexity either although I haven't yet seen the file Eos produced.  You are correct, sfpx, that a relationship is better approach but only if it does not load the schema with extra clutter and complexity.  I believe the best method, since you only need this for another script and not for display,  is to use a script.

 

Method #1 semi-fast summaries

 

No, Fast Summaries will not work in this case (I was just thinking out loud visualizing loop where we can short-circuit and not loop all records).  This requires no calculations, custom functions, portals AND you can remove the sort from all relationships (which can be expensive).

 

It will not be quite as fast as Method #2 because it loops the WorkEvents records but it stops as soon as it has identified all of the Workers for a Contract. It also means you can ditch the pn (person name) calculation from WorkEvents since it loops records and can gather that information through the Workers relationship.

 

Method #2 sort relationship

We don't even need to leave the Contracts layout at all and we can instead loop a variable.  WorkEvents must be sorted descending on ct (creation time). This does use the pn calculation already existing in WorkEvents.  All sorts from the other relationships can be removed as can all the other calculations, portal etc.

UPDATE:  In now reviewing all files and methods, the semi-fast summaries (method #1) seems the leanest.

I present the result in a comma string but of course $result is a regular list as you requested.

 

Thanks a lot Laretta for taking all this time to even document the scripts. Much appreciated.

 

Method 2 is basically what I'm currently testing but instead of getting the list of Workevent:pn in a script step while in the Contract layout I have the list as a calculated field in the Contract table so I don't have to move to the Contract layout to get the list.

 

I'm not sure why you were so against the idea of this calculated field...as far as I know the field is not calculated unless it's being displayed or used in script or in another calculated field. 

[LaRetta]

Calculations should only be added to a table if it is needed for display or export IF it can't be handled another way just as easily.  All calculations certainly DO have weight not to mention increased length of table.  You said you needed this information for another script so why not simply gather the values when needed instead.  :-)

 

fixed typo

Edited November 28, 2014 by LaRetta

[sfpx]

Calculations should only be added to a table if it is needed for display or export IF it can be handled another way just as easily.  All calculations certainly DO have weight not to mention increased length of table.  You said you needed this information for another script so why not simply gather the values when needed instead.  :-)

 

You mean "if it can NOT be handled another way..."

Are you telling me that a calculated field that is not used will still impact the performance ? 

 

The thing is that I need to get that list from any related record below Contract...

So I'm afraid that using GTRR to Contract to get the list and going back to the original layout would slow down the script uselessly.

[LaRetta]

Well if requirements keeps changing ... 

 

The GTRR isn't to Contract.  You can GTRR through any of your entity-style diagram - up or down and the results will be the same.  And it is as fast as an indexed find.  In fact, you hopefully have layouts without ANY fields or references to use as a super-highway to isolate records.

 

But I am not going to try to convince you of anything.   :-)  

[LaRetta]

 

Are you telling me that a calculated field that is not used will still impact the performance ? 

 

Although I hate to repeat myself, yes it does.

[sfpx]

Well if requirements keeps changing ... 

 

The GTRR isn't to Contract.  You can GTRR through any of your entity-style diagram - up or down and the results will be the same.  And it is as fast as an indexed find.  In fact, you hopefully have layouts without ANY fields or references to use as a super-highway to isolate records.

 

But I am not going to try to convince you of anything.   :-)  

 

Not sure what you mean by going to any entity, up or down, would give me the same result.

List ( WorkEvent::pn ) will not give me the same result If the previous script step has moved to the Work layout than if it has moved to the Contract layout (if there are more than 1 work for that contract of course ...).

 

Maybe I misunderstood what you were trying to say.

 

If I want the list of unique workers who worked for a particular contract I have to move to the Contract layout.

 

Although I hate to repeat myself, yes it does.

 

Well that's interesting. 

That would be worth a new different thread. 

[LaRetta]

Not sure what you mean by going to any entity, up or down, would give me the same result.

If I want the list of unique workers who worked for a particular contract I have to move to the Contract layout.

 

Well of course you would need to move to Contracts if you want the result based upon Contracts - everything is perspective in FM.  But if you want the result based upon Work, the same theory applies and the result would work the same. Changing layouts is not a bad thing!!  And since this request is only used in scripts, what difference does it make?  You have not given us any idea of what 'the script' involves.

 

I assumed you were staying on the Contracts layout, as did Eos by using portals.  Again, fluctuating requirements ...

 

I believe you have many options to choose from now. :-)

And honestly, I don't care which you choose.  

[sfpx]

Well of course you would need to move to Contracts if you want the result based upon Contracts - everything is perspective in FM.  But if you want the result based upon Work, the same theory applies and the result would work the same. Changing layouts is not a bad thing!!  And since this request is only used in scripts, what difference does it make?  You have not given us any idea of what 'the script' involves.

 

I assumed you were staying on the Contracts layout, as did Eos by using portals.  Again, fluctuating requirements ...

 

I believe you have many options to choose from now. :-)

And honestly, I don't care which you choose.  

 

Nahh no fluctuating requirements just me being not precise enough from the get go 

 

Anyway, like you said I have enough options so the discussion can end here .

 

Thanks to you , Eos and David for your inputs.

[LaRetta]

Hey, you still may get others responding with different ideas even yet ... that's the beauty of this forum!  I am greatly opposed to adding a calculation to a table (or custom functions or portals etc) when a script can handle it as easily.

[LaRetta]

So to compare:

 

You have a custom function, two additional calculations (person name and your list calc in Contracts) AND you sort the relationship.  Instead, Method #1 just leaves the layout and returns and it CAN be fired from anywhere as long as you gather the ContractID (if that is the result you are wanting).  Anyway, have fun with it!

[sfpx]

So to compare:

 

You have a custom function, two additional calculations (person name and your list calc in Contracts) AND you sort the relationship.  Instead, Method #1 just leaves the layout and returns and it CAN be fired from anywhere as long as you gather the ContractID (if that is the result you are wanting).  Anyway, have fun with it!

 

The relationship was already sorted but I perfectly understand your point and will test method 1 later today.

[David Jondreau]

Of course SQL will work but I'm trying to avoid using ExecuteSQl as much as possible as it tends to be slower than using FM relations.

I don't think a properly constructed SQL statement is slower that a properly constructed relationship.

 

I personally don't use ExecuteSQL() a lot, but I consider that a weakness in my programming. Despite that, I would use SQL here. The requirements are straightforward and it would save you a bunch of scripting.

 

The simplest way is to store both the ContractID and the Worker Name in the WorkEvents table and do the SQL on WorkEvents. (SELECT DISTINCT WorkerName from WorkEvents WHERE ContractID = ? SORT BY ct"

 

*I'm not sure if the SORT BY is necessary.

[sfpx]

I don't think a properly constructed SQL statement is slower that a properly constructed relationship.

 

I personally don't use ExecuteSQL() a lot, but I consider that a weakness in my programming. Despite that, I would use SQL here. The requirements are straightforward and it would save you a bunch of scripting.

 

The simplest way is to store both the ContractID and the Worker Name in the WorkEvents table and do the SQL on WorkEvents. (SELECT DISTINCT WorkerName from WorkEvents WHERE ContractID = ? SORT BY ct"

 

*I'm not sure if the SORT BY is necessary.

You mean ORDER BY

It's necessary and that query would not work as expected.

I tried to do it by SQL yesterday just for testing and it's more complicated than it looks.

 

ex: "SELECT DISTINCT WorkerName from WorkEvents WHERE ContractID = ? ORDER BY ct desc"

The ORDER is done after the set has been reduced to Distinct values therefore the result is not what we want. We get distinct names but not in the correct order.

I searched on the net and the query should have the form of 

 

 

SELECT _ FROM (

SELECT _ FROM _ WHERE _=_ ORDER BY _ DESC

) AS _ GROUP BY LOWER(_)

 

But this kind of query from subquery is not supported by FM.

 

I got a SQL query to work but it was using a calculated field in the worker table that was getting his last workevent.

 

Something like

 

ExecuteSQL("

select DISTINCT Name from  WorkEvent, Worker where workevent.id_contract=? and Worker.WorkerIdPk = WorkEvent.WorkerId  order by bct desc";

 

"¶";"";

 

ContractIdPK)

 

where bct is a calculated field in the Worker table that gets the worker's latest workevent

 

It works but it's using a calculated field so I dismissed it.