Using "related Field" Of Relationship In A Details Report Datapage

[financemy]

Hi there - quick question about relationships.

 

I've created a relationship between two tables using the Relationship Settings dialog, and set the "Value for display in Related Field" to the desired field.

 

This works great - in the table view, I can see this lookup happening no problems (this substitutes the integer values 0/1 for the text values "Single"/"Married").

 

When I use the table as a datasource for a Report Details Datapage, I see the original value ("0"), not the looked-up one ("Single").  Is this by design?   How can I set the report to show the looked up value, not the original?

 

Thanks

Mark

[financemy]

After letting this bubble around my brain for a while (which didn't take long  ) I solved it by just doing a SQL inner join in a calculated field:

SELECT name FROM fm_intervals WHERE fm_intervals.value = [@field:client1_income_interval]

Makes much more sense to do it this way - it is far more elegant and allows me to make changes to my table without having to change my datapage each time.

 

[Jan]

Hi Mark,

 

As far as I know, you can create a View and use it as a DataSource.

You can find instructions in this article.

 

I hope, it helps.

[financemy]

Hi Jan, thanks for the reply - yes that works, however only for the first looked up value in the view that is from the same lookup table.  

 

One of my tables uses duration/name pairs (e.g. 1/Yearly, 12/Monthly, 4/Quarterly, 365/Daily etc) and this is used multiple times on the datapage report detail (income and expenses for a client).  I can join the first duration entry fine (and it works) but subsequent ones result in a view with no data.

 

Caspio support suggest s using virtual fields and rules to hide or show the text; to me this isn't very elegant as it negates good database and programming practice for a lookup table rather than hard coding values

 

Thanks

Mark

[MeganB]

I am also having this exact problem, so thanks for the reply Financemy.  Would you mind elaborating on the table and parameter structure in your example? What would push the @field parameter? Would it be coming from a field in the search section in the same datapage?  Sorry if these questions are dumb, I'm still a bit of a noob at SQL/caspio...

[Jan]

Hi MeganB,

 

If I understand correctly, [@field:FIELDNAME] - is the value of the field on the Result page.

You can use the Parameter Picker to add this parameter (the Data Source Field section) to a Calculated field.