I'm getting to grips with using drupal entities in data blocks - My hope is to eliminate joins to retrieve field values and also to be able to retrieve field labels (in the correct language)

I am connecting to submissions a entityform of type 'protective_factors_questionnaire' with the following code:

SELECT entityform_id FROM entityform
WHERE type='protective_factors_questionnaire'

--INFO
; This demonstrates loading a custom entity.
entity_type = entityform
entity_id = entityform_id

The data displayed is the specified collumns from entityform and all the field values.

I want to:
- specify fields in columns
- bring in the field names (in the correct language assuming they have previously been translated)

If I attempt to specify fields by label or machine name in the block they are rejected as errors. The drop-down select that appears as you type in the Query editor offers the tables i.e. field_data_field_etc... rather than the field values.

I realise that I can chose not to include a field in a report, however -
- it can be a very long list of things to 'untick'
- i need to unpivot the table - transpose columns to rows so as to present results in a radar graph (UNION ALL)

I realise that this may be beyond the scope of what was intended and I can achieve almost all of this with a standard SQL block - I am exploring drupal entities to see if it makes things more maintainable or effective.

The one thing I can't do in SQL is bring in the labels since they are buried in a php array - as far as I can tell the only way to have internationalised labels is to create another field whose value is the text of the label and use entity_translate to translate that content and to then select that field and mark it as a label in the report.

Comments

mylesorme created an issue.

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented

I could think of a patch that might solve that problem using data contexts, and will try and get to it but I'm not sure I'll be able to do this on your timeline.

One thing that may help is that translations are available to any database field: http://forenasolutions.org/reports/help.reportingfrx#fields

So you could put the english versions of the field labels as hard coded values in your data blocks and then let the Transaltions api translate them for you to the proper value. Of course, you can also create separate translated versions of each report in different languages, and translate the whole report as well. Because Forena supports arbitrary markup, this was the only way that I could think of to support internationalization in a sane way. For example if you create a sv/myreport.frx file, then that frx file will be rendered as the Swedish version of the report.

When working with foreign clients I most often use the latter approach because it facilitates adding more bulky instructions in each report that can be translated.

mylesorme’s picture

mylesorme CreditAttribution: mylesorme commented

Oh, ok - I was planning a slightly different approach. For reports with pragraphs that vary based on a score result of a questionnaire I was thinking of using blocks for the different sections that pulled in the appropriate content in the correct language (translated using the usual Drupal translation facilities, which I am still getting to grips with)

I'll persue your suggestion on fields - interestingly I currently have the labels hard coded in the block (as in Select 'Personal Competencies: Self Concept', field_self_concept_value From etc) but I couldn't get it working with the Translations api - I'll work through the help you reference today with glee.

I'll use a SQL block, which although using lots of joins works perfectly well, so please don't rush any patches - perhaps we could work it into a desired feature for a future date.

Many thanks

mylesorme’s picture

mylesorme CreditAttribution: mylesorme commented

Can't seem to delete the comment - I commented then came to a different understanding - please accept my apologies.

mylesorme’s picture

mylesorme CreditAttribution: mylesorme commented

So far here is what I have done

In the SQL there is a column, hardcoded, called 'Term' - I wish to translate these values.

By harcdoding I mean that I have them within single quotes in the SQL statement of the data block:

SELECT 'Social Bonding: Family' AS Term FROM...

Is this what you mean by 'hardcoding'?

In the .frx I have this:

<frx:fields>
  <frx:field id="Term" format="drupal_translation" format-string="" target=""/>
</frx:fields>

I'm expecting the hardcoded values to show up in the list of strings to translate - so far they do not.

Should I be looking elsewhere?

I have created a text format called "reports" as recommended elsewhere in the docs, and in string translation settings allowed this format to be translated.

Do I need to specify the format-string?

I note the following from the help file:

Specify a field that contains the serialized data used for translations (e.g. watchdog table). Normally you can leave this blank.

How do I find the field that contains the serialized data

mylesorme’s picture

mylesorme CreditAttribution: mylesorme commented

I note that I am unable to tranlate anything but the title of the report when clicking on the 'translate' icon next to the report - I've put in some text to test. So perhaps Drupal is having problems registering the strings - I'll investigate this avenue for a while.

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented

Regarding literal columns, or "hardcoded" columns. You may find better luck using column aliases that are all lowercase (.e.g terms in stead of Terms), but you basically have the right of it. One of the challenges with Drupal translation strings is that the strings won't show up as registered until you view the report in another language. You can't actually view it in english and then specify the translations. You do not need to specify serialized data for your literal labels. That is only for when the strings to be translated have tokens/placeholders in them.

When making a "translated report" the way you discribed it, Forena will not "do" the translation for you. You are on the hook for altering the "translated" report into a different language. In this case the drupal tranlsation api is not used. As I indicated before, the translated report ends up being a completely separate file in a language specific folder. If you are editing the report with drupal, you need to change languages to your desired language in order to edit the translated version of the report.

Hope that helps.

mylesorme’s picture

mylesorme CreditAttribution: mylesorme commented

Thanks for your patient explnations - I seem to be getting the hang of it and shall continue to explore.

mylesorme’s picture

mylesorme CreditAttribution: mylesorme commented

I've changed literals to lowercase but they are still not showing up in the list of translatable strings or fields- I am looking at the form in a non-default language - but it strikes me to ask how Drupal knows that this is not the language in which the literals were written.

When I place the token {report.language} within the body it displays the language - as I switch from one knguage to another it changes - so the language is not fixed. What prompts Drupal to register the strings?

To obtain different language reports does one place them in diffirent folders manually or does ferona do this when the report.language is set in the report context?

I'm sorry that this has wandered from the original issue topic to one on language.