TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
Relationships on a Number Field
I'm not sure why, but you can't have a relationship on a numeric field for the second table. I really don't understand why you can't. I have a relationship that I am having to cobble together just to get it to sort correctly.
User Experience

Michael Ver Duin
Date Created
3/27/2011 7:38:16 PM
Date Updated
3/29/2011 11:38:26 AM
Not Planned
Promoted By
Michael Ver Duin
Slava Shinderov  Staff  3/28/2011 5:37:18 AM
System can create relation using ANY physical (not a formula) column, the only requirement is that key and reference columns must be of same type.
Please note that AutoNumber is actually a text column:
Michael Ver Duin 3/28/2011 8:11:15 AM
That is the problem I'm having then. Dumb question: Why do they have to be the same?
Also, it would be nice then to have a 'fake' column that you could build a relationship on that treated the auto number as a number. again, I've found a work around, but it isn't as smooth.

Thanks for the link.
Michael Ver Duin 3/28/2011 8:12:26 AM
I guess this can be removed from the ideas, seeing as it is working correctly.
Kirill Bondar  Staff  3/29/2011 9:48:46 AM
Autonumbers are not really numbers since they allow prefixes, suffixes and date parts in it. You can format autonumber as, say REC-{YY}-{MM}-{DD}-{000} and get sequences like REC-11-03-29-001, REC-11-03-29-002, REC-11-03-29-003, REC-11-03-30-001, REC-11-03-30-002 - sortable, by the way.

{0} formatting TeamDesk generates by default serves the only purpose - it uniquely identifies the record. It does not need to be sortable or searchable.
Michael Ver Duin 3/29/2011 10:26:14 AM

We use a second autonumber field increment a number that is unique to each job in our system. Therefor it does need to be sortable numerically for me. I've found a work around by sorting first by date entered, then by our "OrderID" that way there is only a screw up on a day when it adds another digit, so very very rare at this point.

I do however see the value of the text nature of this field. Thanks for the replies.
Kirill Bondar  Staff  3/29/2011 10:39:11 AM
Another way is to create Formula - Numeric that performs ToNumber([Autonumber]). Use it to display and sort the values and let autonumber to generate new codes.
Kirill Bondar  Staff  3/29/2011 10:40:56 AM
Also, Date Created column stores the values with millisecond precision (despite only seconds are displayed) so it's values are sort of unique.
Michael Ver Duin 3/29/2011 11:38:26 AM
Formula, Neat, I might try that.

We are using a 'date entered' based on Today(), so not a 'date created' we imported about 11,000 orders from an old system, if I sorted by the system field date created, it wouldn't work for all the older orders. Thank God I had the Date Entered field.
Back to Search Results