Calculation result in reference data csv ?

Answered
Avatar
Ken Maguire

I have uploaded 3 simple csv files.

In my app

  1. the user makes selections from 4 dropdown fields. Each selection is then referenced in 2 csv files.
  2. 4 Number fields show the results from the above selections.
  3. 2 calculation fields then each multiply 2 of the results above together.

Everything above works.

Next I need to search for each resulting number from above in a 3rd csv & return the corresponding words. I cannot get this part to work. It seems that it is not possible to search for calculation results in reference data.

FYI, everything is on the same screen.

Thank you

Ken

 

0

Comments

18 comments

Sort by Date Votes
  • Avatar
    Keith McQuait

    So you're saying, you have a calculation field that returns a number, let's say 22 for example, and you have a reference data file with the number "22" in columm A and in column B the words "twenty-two".. am I reading that correctly?

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait
    • Edited

    If that's the case, it seems logical that it would work but since it isn't I'm thinking it has something to with the data type in the .csv file. ie. Text vs. Numbers. In other words, it may not be recognizing the calculation field number 22 as equal to "22" text value in the .csv..

    just a guess.. I'm sure the GoCanvas Community Team will know. 

    0
    Comment actions Permalink
  • Avatar
    Chip Phillips, Community Manager

    Howdy Ken,

    Great question! Keith is on the right track concerning how the output of a calculation field cannot be directly used for reference data. However, you can use the MIRROR backdoor as a workaround. This backdoor allows you to mirror data from a field on a previous screen and output the value as another field type.

    In the example below, I have a screen called Calculation, where the user will input two numbers and the resulting total is calculated and displayed:

    On the second screen (Total Name), I have set up two fields:

    1. A short text field that uses the MIRROR backdoor to copy the value from the original 'What's the Total?' calculation field and output the data as short text. 
    2. A short text field that uses the mirrored field as a reference to output the corresponding word to each numerical value.

    For the mirrored field, you will need to make sure to name it identically to the calculation field on the first screen. Then, set the Reference Data section to refer to the column of numerical values from your CSV file:

    Finally, include the text MIRROR in the Receipt Label section under 'More' on the left side of the screen:

    For the second field (Total in Text), set up the reference data section to pull from the corresponding words column in your CSV file based on the value from the mirrored field:

    Here is what the above app build looks like in action:

    Calculation Screen

    Total Name Screen

    You can also use the Mobile Visible setting to hide the mirrored field on the second screen. I hope this helps!

    2
    Comment actions Permalink
  • Avatar
    Ken Maguire

    Thanks a lot for replying. But I'm still in trouble. I should have mentioned that my screen is looped. When I tried your solution the first screen does not appear in the "Mirror from which screen?" dropdown.

    0
    Comment actions Permalink
  • Avatar
    Chip Phillips, Community Manager

    A looped screen does change our approach. The MIRROR backdoor can only mirror a completed field from a previous screen. Because of the way the backdoor pulls data, this solution will not work with looped screens.

    I have reviewed your Job Safety App and I have another potential workaround. I see that your Risk Matrix CSV file bases the risk rating on a particular score range:

    1-3 = Low
    4-9 = Moderate
    10-15 = High
    16-25 = Catastrophic

    From a high level view, you could use conditional fields to display the rating word based on the calculated score. Since your loop screen is set to Grid Enabled, the rating words could be displayed as potential options with an X marking which rating applies.

    I have made a copy of your Job Safety App to show as an example. To start, I removed the Risk Rating field and replaced it with four short text fields (Low, Moderate, High, Catastrophic):

    For each of these fields, I made 'X' the default value and set conditions based on the score range previously outlined in your reference data. Using 'Moderate' as an example, I set this field to show up when the score value (calculated in the RRB field) is from 4 to 9:

    While completing a submission, the resulting rating field will appear after the user makes their selections and the score is calculated:

    Using the PDF Designer, you could arrange the data from the looped screen to appear as a sort of checklist in the grid:

    I understand this solution is a bit more complex, but the looped screen prevents the use of the MIRROR backdoor, so we need to get a bit creative. I hope this helps!

     

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait

    Wow! Chip, your level of effort on this is greatly appreciated! I know that took quite a bit of time. Thanks!

    0
    Comment actions Permalink
  • Avatar
    Ken Maguire

    Thank a lot Chip. That's a great work-around.

    This works ok on android but there seems to be an issue with execution on the windows version.

    Also, is there any way to prevent certain fields from displaying in grid view ? With all the extra fields the grid looks very messy.

    Many Thanks 

    Ken.

    0
    Comment actions Permalink
  • Avatar
    Chip Phillips, Community Manager

    Thank you for the kind words, Keith and Ken! Glad we were able to find a workaround.

    Concerning the grid view, you can customize which fields will appear on the grid from the PDF Designer. You can remove or resize each column in a grid, as well as drag-and-drop fields to make multiple tables.

    Here are some links to Help Center topics on how to customize tables in the PDF Designer, as well as how to set up a table when a loop has more than ten fields:

    https://help.gocanvas.com/hc/en-us/articles/115006655327-How-to-customize-tables-in-the-PDF-Designer

    https://help.gocanvas.com/hc/en-us/articles/115006848328-What-to-do-if-your-table-in-the-PDF-Designer-has-more-than-10-fields

    What sort of issues have you hit when completing submissions on the GoCanvas Windows client? Could you confirm which version of the of GoCanvas you are running on your desktop?

    You can find the version number for the Windows client on the login screen by clicking the Settings tab, towards the bottom of the window.

    0
    Comment actions Permalink
  • Avatar
    Ken Maguire

    Hi Chip.

    I've tried it on Windows 7 & 10 with the same results.

    Once I have selected likelihood & consequence, I expect one of the hidden short text fields to appear instantly, but it doesn't. I continue filling in the next field then the hidden field appears. Same thing happens when I select the the other likelihood  consequence fields. This doesn't happen on Android.

    0
    Comment actions Permalink
  • Avatar
    Ken Maguire

    I've been experimenting with the PDF designer. I added a company logo & made a custom pdf layout but the final pdfs on the tablet or in the submissions do not update. The pdf looks ok in the pdf designer preview & I've saved it. How do I implement it?

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait
    • Edited

    New submissions should reflect your changes. I don't think changes to the PDF designer will retroactively change the previously submitted items (from before the change).

     

    0
    Comment actions Permalink
  • Avatar
    Ken Maguire

    Hi Keith,

    New submissions are not reflecting the changes.

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait
    • Edited

    Not sure why that would be happening unless you made an app change and the app is not published. Even if you didn't change the app, you could try making a slight change to the app and re-publishing and see if that makes a difference.

    FYI. There is also an option from within the app using the 'history' tab. You can open previously submitted apps and edit them. However, when you do this, the output the user sees on the mobile device or in the email received doesn't reflect the changes. You (or the user) must go online to gocanvas.com/submissions and use the 'view' or 'download' the 'Designer' version to get the updated submission.

    Not sure, but this process of using the 'history' may retroactively update your old submissions (as long as the version hasn't changed).. 

    0
    Comment actions Permalink
  • Avatar
    Ken Maguire

    Thanks Keith.

    As I'm still developing the app, so I'm not bothered about the older submissions. Just need it to work on new submissions. I've had over 100 revision changes to the app & republished each time. The app updates but the pdfs are still displaying the default layout.

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait

    Like the 'standard' output?  data aligned left with a bunch of dots over to the answer? 

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait
    • Edited

    Been a while since I looked at a standard PDF. I just did and I guess the standard PDF doesn't exactly look like it used to.. Anyhow, if that is the case, you have to switch your output to 'Designer' instead of 'Standard'. It's a little confusing because you would think the option for 'Standard' or 'Designer' would be in the PDF settings but it's not, it's in the email options.

      https://help.gocanvas.com/hc/en-us/articles/115006654807-How-to-switch-between-the-Standard-and-Designer-PDFs

    1
    Comment actions Permalink
  • Avatar
    Ken Maguire

    Thanks Keith.

    I would never have found that without your help.

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait

    yw! Glad I could help!

    0
    Comment actions Permalink

Please sign in to leave a comment.