relationship field type

[Steve]

EDIT: 8/14/14 - Corrected mistakes I just noticed in the DB definitions below.

I'm attempting to produce an aging list view of jobs.

Two tables:

CustomerList
CompanyName(Text)
Jobs(Relationship), CustomerList.CompanyName AND Job.CompanyName, Show last Job.Location
Today(date), use current date
LastJob(Relationship), CustomerList.CompanyName AND Job.Customer, Shows last Job.Opened.
ActiveStatus(calculated), Today - LastJob # LastJob = 12/23/13 and today = 08/08/2014 displays: 40385 rather than no of days
...

Job
Opened(date)
Customer(DB Popup), CustomerList.CompanyName
Location(Text)
...

To test what I thought was happening i created a single DB:
FirstDate(date)
LastDate(date)
Elapsed(Calculated), LastDate - FirstDate
Using the same dates as above ActiveStatus, Elapsed displays: 228.00
On the assumption that Relationship is returning a text value I changed FirstDate to type Text, which caused Elapsed to display 40385.

SO it looks like the display info returned by the relationship field is in text form rather than date, (in this case).
Is this a bug?

If not, would it be possible to add conversion functions to the Calcuated field's function list??
OR cause the Relationship field to return the data type of the selected show field???

OR I'm missing something entirely,... which wouldn't be the first time.

[Brian_Houghton]

Hi Steve,

Sorry for missing your initial question.

The relationship field pulls date values across as text, so to get the actual date in a number format that is usable you would have to add a field to the related table. The field would be a calculation and it would have the formula Date + 0 and the output as an integer.

You can hide this field if you want.

Then configure the relationship field to pull the integer field and use it in your calculation. You can do this for other date fields needed as well.

Hope this helps.