If a view contains an alias like

RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName

And the field has a definition in custom_captions & custom_edits

Caption Family: Colnames

Caption Code: pers_FullName

Then the field is displayed.

If the field is derived but lacks a definition in custom_captions & custom_edits then the field will not be displayed.

This is reasonable as the SData interface obeys field level security and so requires a definition for the field in custom_edits.

If you have developer program membership then you should be able to download the derived fields assistant. This should make the job of entering the required meta data very easy.

community.sagecrm.com/.../27464.aspx

I think you need to make sure that the column is described like a regular column using the column prefix.

E.g.

SELECT *, 'somestring' AS xxxx_description

This assumes that XXXX is the table prefix.

And I've just corrected my post so it is as if I never make mistakes.

Thanks Jeff.

I found your article on the subject too (community.sagecrm.com/.../adding-derived-fields-in-views-into-meta-data.aspx).

Following your advice, I have found an existing field definition as follows:

Caption Family Caption Code UK Translation

ColNames description Description

I then change my view to:

CREATE VIEW vMyEntityTest

AS

SELECT *, 'somestring' AS description

FROM MYENTITY

And it should now work?

But it doesn't.

Magic! Thanks Jeff.

Just shows how important that table prefixing is.

Also, just noticed in your original answer, you switched between Pers_FullName and FullName. Probably worth correcting this to avoid confusion by any future reader.

I've added definitions in Custom_Captions & Custom_edits (and FieldSecurity) for my custom view's derived field, but it is still not showing up in the sData schema. The derived field uses the same prefix as all the entity's non-derived fields. Metadata has been refreshed.

What additional magic incantations might be required in order to get this to work as described?

Sorry, I can't remember how I got it to work last time. It definitely requires some incantation / rain-dancing / ritual sacrifice. I went back and recreated the view that I thought was previously working, which does have the colnames definition, but it no longer works.

What I can offer as a hacky workaround is to use a column name of a completely unrelated table. The following works:

CREATE VIEW

AS

SELECT .*, 12345 AS comp_companyid

FROM

LEFT JOIN Opportunity

ON ._opportunityid = Opportunity.oppo_opportunityid

This also works:

CREATE VIEW

AS

SELECT .*, Opportunity.oppo_opportunityid * 100 AS comp_companyid

FROM

LEFT JOIN Opportunity

ON ._opportunityid = Opportunity.oppo_opportunityid

Here I'm multiplying the opportunityid by 100 to get a derived field.