Reporting on h_sys_accounts

[QEHNick]

I'm creating a report to collate some information on our customers using the request table and the h_sys_accounts table. 

Within the h_sys_accounts table are the custom attributes. When viewed in the "database Direct" tool, I can see the "Attribute X"  values (one thru eight) are present.

When using the report, none of those values appear. Is this a limitation or is there something I am missing?

[NeilWJ]

Hi Nick,

Columns seem to be present as far as i can tell. How are you creating the report?

[NeilWJ]

and in the data....returns ok.

[QEHNick]

Sorry @NeilWJ, I meant there is no data returned. The fields are there to select.

 

This goes for any attribute.

 

 

 

[NeilWJ]

and that sql clause you have specified in the table join section yes? Can you put up screen of your select tables and join so i can make sure mine is the same

[QEHNick]

 

This is without a filter or ordering.

[NeilWJ]

@QEHNick ,

Works ok as far as i can tell.

You sure the sys accounts login id matches the requests customer id?

Set the JOIN type to "right join" - this will then only return records if a match exists in sys accounts table.

Also maybe add those 2 cols (maybe accounts userid as well) to output so can see.

Lastly for now maybe filter report to select just 1 request by request ID where you know the request customer id = a sys account login id that has attribute fields set.





 

[QEHNick]

That suggestion helped me figure it out - the customer ID and Login ID is the same - however, the accounts which are like that do not have any custom attributes. I have instead selected "h_fk_user_name" from the request table and "name" from the h_sys_account table. These match perfectly and the results are now correct.

 

Thanks for the mental nudge @NeilWJ