I can't get any value from a Custom Field that keep a date data type

Hi, I’m trying to get a date value from a Custom Field, but just null (blank) is the result. I created the field as text as data type, and I already see the field, but has not any value in there.
The login user has all the permission in Asana.
Thanks in advance.

Hi @Daniel_Bernal and welcome to the forum,

We’re going to need more detail in order to try and help out. Please post the code you’re using to retrieve the custom field and also post the result you’re getting back. Thanks!

1 Like

Hi Phil, thanks for reply.
This code is Qlik Sense programming, very close and similar to an PL/SQL. This is an extract of the main query that I use to retrieve the data:
Projects:
LOAD
id_proyecto,
“Campo Personalizado”,
“Valor Personalizado”,
if(“Campo Personalizado”=‘Fecha Prevista Inicio’,“Valor Personalizado”,null()) as ‘Fecha Prevista Inicio’
FROM [lib://Asana QVD/*.qvd] (qvd)
;

Where “Campo Personalizado” means Custom Field, and *.qvd means all qvd files (files from Qlik Sense, similar to an Excel file) that keep the data from the main query (API).
After that, I create a table with id_project and custom field:

FPI:
Load Distinct
id_proyecto,
[Fecha Prevista Inicio]
Resident Projects;

That table give me the possibility of use that column in the report.
Finally, I create the report, but the custom field is empty:

If you need more information about the issue, just let me know.
Thank you!

Unfortunately that code isn’t going to help me much as it’s very foreign. I don’t see anywhere where you’re actually issuing any query to Asana.

I highly recommend first using a tool like like Postman to get it working. Then I think it’ll be easier to translate that to your specific environment.

Hi Phil, thank you again for your answer.
This is the query that I get from ASANA:

RestConnectorMasterTable:
SQL SELECT
“gid” AS “gid_u12”,
“created_at”,
“current_status”,
“due_on”,
“due_date”,
“modified_at”,
“name” AS “name_u11”,
“start_on”,
“__KEY_data”,
(SELECT
“name” AS “name_u1”,
“number_value”,
“__KEY_custom_fields”,
“__FK_custom_fields”,
(SELECT
“name” AS “name_u0”,
“__FK_enum_value”
FROM “enum_value” FK “__FK_enum_value”)
FROM “custom_fields” PK “__KEY_custom_fields” FK “__FK_custom_fields”),
(SELECT
“name” AS “name_u6”,
“__FK_followers”
FROM “followers” FK “__FK_followers”),
(SELECT
“name” AS “name_u7”,
“__FK_members”
FROM “members” FK “__FK_members”),
(SELECT
“name” AS “name_u8”,
“__FK_owner”
FROM “owner” FK “__FK_owner”),
(SELECT
“name” AS “name_u9”,
“__FK_team”
FROM “team” FK “__FK_team”),
(SELECT
“name” AS “name_u10”,
“__FK_workspace”
FROM “workspace” FK “__FK_workspace”)
FROM JSON (wrap off) “data” PK “__KEY_data”
WITH CONNECTION(Url “https://app.asana.com/api/1.0/projects/$(vIdProyecto)”);

where $(vIdProyecto) read every project id previously loaded in that variable.

After that, I create this table:

[custom_fields]:
SELECT [name_u1] AS [Campo Personalizado],
[__KEY_custom_fields],
[number_value] as ValorNumerico,
[__FK_custom_fields] AS [__KEY_data]
FROM RestConnectorMasterTable
WHERE NOT IsNull([__FK_custom_fields]);

The problem appears with the fields created as text data types, not with the fields with number as data type.
Field [name_u1] has not any values.
I can’t understand why this custom field has no value.

I’ll really appreciate if you can check this.

Thanks!

From what I can see here, you’re only retrieving the number_value from the custom field record, which would explain why text values are blank. Text values are stored in their own separate field (as are enum values).

The following is pseudocode as I don’t know your language, but I think you’ll need to do something akin to this (my changes are in bold italics):

SELECT [name_u1] AS [Campo Personalizado],
[__KEY_custom_fields],
[number_value] as ValorNumerico,
[__FK_custom_fields] AS [__KEY_data]
FROM RestConnectorMasterTable
WHERE NOT IsNull([__FK_custom_fields]) AND [resource_subtype] = “number”;

SELECT [name_u1] AS [Campo Personalizado],
[__KEY_custom_fields],
[text_value] as ValorNumerico,
[__FK_custom_fields] AS [__KEY_data]
FROM RestConnectorMasterTable
WHERE NOT IsNull([__FK_custom_fields]) AND [resource_subtype] = “text”;

@Phil_Seeman and @Daniel_Bernal,

I think this may be able to be simplified a bit by using display_value instead of the several conditional ones specific to type. See more at: https://developers.asana.com/docs/custom-field

Thanks,

Larry

1 Like

Good point, @lpb! In Flowsana I always have to deal with the underlying gid’s for enum values so I never use display_value but this looks like an excellent use case for using it.

1 Like

Hi Phil, thank you again.
I understand what you mean. In the API code, i tried to write the sentence but it doesn’t work, I’m sure that is a sintaxis problem (this is just a part of the entire text):

(SELECT
“name” AS “name_u1”,
“number_value”,
“__KEY_custom_fields”,
“__FK_custom_fields”,
(SELECT
“name” AS “name_u0”,
“__FK_enum_value”
FROM “enum_value” FK “__FK_enum_value”
WHERE “resource_subtype”=“text”)
FROM “custom_fields” PK “__KEY_custom_fields” FK “__FK_custom_fields”),

Because effectively I read the enum values, and i’m trying to force it to text (bold).
Could you tell me where must i write that sentence?

Thank you

Hi lpb, thanks for answer.

In the code, not exists any part when i can specify the display_value.
It is possible to write it in the API code?

Thank you.

You may be able to replace number_value and enum_value with display_value.

Hi lpb, thanks again.
I’ve trying with your solution but it doesn’t works. But maybe i don’t understand your message. This is the code that i ran:

(SELECT 
	"name" AS "name_u1",
	"display_value" as "number_value",
	"__KEY_custom_fields",
	"__FK_custom_fields",
	(SELECT 
		"display_value" AS "name_u0"
		"__FK_enum_value"
	FROM "enum_value" FK "__FK_enum_value")

I’ll be in holidays from today, so, only if it’s possible, I’ll be back with you next Monday.

Thank you!

Sorry, @Daniel_Bernal, I’m not sure I can be effective helping you; I don’t really know your environment and it’s too hard to debug remotely like this. Sorry about that…