TeamDesk Knowledge Base & Support

anjuim moied 11/28/2012 2:12:53 AM

Hi Brett,

I think but i am not sure if this is what you mean .I would create a relation to the time value that counts the number of records greater then the value of your time field, plus 1 to find your position. Not sure if it would create a cycle so base the value on a hidden field with default the time taken field of the related table.

I think this might work .

Regards Anjuim

basenine 11/28/2012 2:54:26 AM

Thanks Anjuim,

I think I know what you mean - I'll give it a go and let you know!

Cheers

Brett

Shem Sargent 11/28/2012 6:01:31 PM

Hi Brett, just wondering if you had found a solution to this. I need something similar, a rank list with rank order calculated based on scores of currently active records.

basenine 11/28/2012 6:38:15 PM

Hello Shem,

No - I'm sorry to say I couldn't figure out a way yet with Anjuim's approach. Here's the steps I've taken so far.... Lot's of backwards and forwarding!

Step 1: I created a third table called Positions and related it to both the Event table and the Result table. Match condition between Result and Position is [Event]
Step 2: created both lookups and summary columns in Positon...went in circles for about an hour...

This didn't really work out as I need to be able to have multiple relations between the Result and Position and this can't be done any other way than manually i.e. no work flow rule
Step 3: Create a relation between Results table and Results table! I didn't know you could do this but I tried it and you can! The idea here was to again select which Results records were slower than the current record.... Didn't work as you need to be able to do a Multi reference column again.... manually add each appropriate record.... Didn't work ;(


So I gave up and thought I'd sleep on it... looks like I might need a few more hours as I woke up with still no clue - *citation: Phil Dunphy!

I really don't think there is a way of doing this other than using the Index method - here, you'd need to create a summary of [Time] and select the appropriate one via index... This could work ok for 1st, 2nd and 3rd...but 500+ positions. Nup!

So, Anjuim - I've probably not understood all of what you've explained fully.

If anyone from TD support can put me out of my misery with a simple solution...or a "No, currently it's not possible"....

;)

Shem Sargent 11/28/2012 6:55:40 PM

Thanks Brett,

I went through a similar experimentation process. The dead end from #3 that you mention is that even if you set the Reference Column to default to the Record Id, the count always comes back as 1. You can limit the available selections to only the lower race finish times or the higher rank scores, but you cannot pass a count of these 'records available for selection' to a field.

I think the reality is that the internal routine that creates a sorted list view would have to be modified with an option to include the row index of the final result.

Shem Sargent 11/28/2012 6:58:57 PM

I submitted a support ticket to see if they have any suggestions.

Evgen Goncharov  Staff  11/29/2012 4:03:28 AM

Shem, Brett
actually, #3 is the way to go within current TeamDesk capabilities - you should create a many-to-many self-relation on 'Results' table and use match conditions to "group" records by event/race ( [Event]=[Event] ) and to "order" them within groups ( [Time]<=[Time] ), then you'll be able to get "position" using 'Record count' summary column over that relation. That solution would give same "position" for records with same times so you should add some match(es) if you want to handle it differently.

Shem Sargent 11/29/2012 3:15:30 PM

Evgen, I am pleased to report that your suggestion worked! The key was to use a many-to-many relation. Of course, in hindsight, it obvious that I could not get the results with a one-to-many relation since it is has the additional constraint of [Id]=[Id]. That is why the count would come up as 1.

As you mentioned, records with the same rank have the same count of matching records. I was able to create two additional many-to-many relations with additional constraints to match my normal view sort order. I could then subtract the summary field of each relation from the original count and get a perfectly sequential list.

This image shows the way it works:

http://i1340.photobucket.com/albums/o735/shemsargent/Selections.png

basenine 11/29/2012 4:51:11 PM

Thanks for all this Evgen and Shem,

I haven't given it a go yet but really appreciate the pics of how it should go together.

I had tried the multi reference but I thought that you'd need to select each one manually (via the 'Add' button).

Looks like this isn't the case. - I'll let you know
Kind regards

basenine 11/29/2012 8:12:51 PM

So - where I was going wrong... this had me ready to jump off the balcony!!

I was doing a Many to Many 'Reference'.... not a Many to Many 'Relation'

Big difference.

Then I got stuck with [Member Name]<>[Member Name]....
On closer look I saw you'd written it as [Member Name]>[Member Name]

That too made a surprisingly obvious and immediate difference.

All good now so thank you for your help. ;) :)