How to add reference data for more than 15 columns

Answered

Comments

12 comments

  • Avatar
    Shaun Harrison

    Can I add 3/4 refernce data files all with the same site name, each adding different sections of data?

    0
    Comment actions Permalink
  • Hi Shaun,

    Yes, that's exactly what you'd need to do. Make sure the site name column is exactly the same in each file. Then the remaining 14 columns can be different to get you to the rest of the fields. Looks something like this (with actual data in it, of course): 

    Then you'd use that Site Name column as your Reference Field to tie them all together. 

    Would love to see folks who have this up and running - anyone able to share? 

    1
    Comment actions Permalink
  • Avatar
    Matt McPheeters

    While the multiple files works as mentioned, I did discover a way to have all the data in a single file.  This is now my preferred method and is easier to maintain large reference data files.

    If you arrange your columns into rows, then you can reference all of them from a single file provided it's under the size limit.  An example reference file is below.  This uses 2 columns as reference fields (Site Name and Field) and then allows Value column to be dynamically referenced based on Site and then Field.

    In the form, I have a drop down list populated with Site Name.  On subsequent screens I have a hidden short text fields mapped to the reference data with screen/field pointing to Site Name, and with the "default value" set to Field 1, Field 2, etc.  Then the field where I want the reference data to show has screen/field reference data pointed at the hidden field with the default value (Field 1, Field 2, etc) set.  The user then just selects the Site and the screens have the appropriate reference data shown.

    This is a bit confusing, but I made a sample form that demonstrates the functionality.  Canvas has my permission to make public the "Dynamic Column Reference Data" form/reference data from my account.

    2
    Comment actions Permalink
  • Avatar
    Shaun Harrison

    Hi Sara,

     

    Thanks for the reply, being as impatient as I am I just tried it and could see that it does work, A lot of data needed to make it work, I'm thinking of adding all existing sites to a drop down list to speed up despatching, how many values can i have in a drop down list?

     

    Matt, I think for the number of values we need building the form would take a while, but glad to know there are workarounds!

    0
    Comment actions Permalink
  • Hi Shaun,

    I don't know the exact number of values you can have in a drop down, but it's a lot (I pasted in 800 just to test). That said, two things will likely happen if you're going that high: 

    1. Your App will run very, very slowly and it will be challenging to manage in the App Builder
    2. It will be challenging for your users to find what they're looking for

    But, if you're at around 50 rows you should be okay. 

    It's really a question of where you want to manage the data. For me, Excel is easier because it can be sorted and search easily (for example, if you needed to update one site name, it's a lot easier to find that in an Excel sheet). You can still use Reference Data non-dynamically to just upload and keep that list up to date as well. That's especially useful if you need the same info populating in more than one App. 

    0
    Comment actions Permalink
  • Matt, I'm in awe! I've never seen it done that way - makes total sense and seems so much easier to manage! Thank you so much for sharing!

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait

    Sara,

    In your example above, you show 3 sheets. Each has 5 site names down the 1st row then 14 columns of data. Are these 3 different saved .csv files?

    What I'm facing is exactly what Cynthia (from Canada) was asking about a couple weeks back.

    If I have more than 15 columns of data that I want to automatically populate answers into fields in the app based off of one answer selected by a user, how do I cross the reference data from one sheet to the other?

    In your example above, must the user select the answer in the app 3 times in order to use the three different reference files?

     

    0
    Comment actions Permalink
  • Avatar
    Sara Kaplow, Community Manager

    Hi Keith,

    Yes, those are 3 separate CSVs. The user does NOT have to select the answer 3 times, you just need to make sure that the original column they're choosing from exists in all of your sheets. In the App Builder, you'll just want the Reference Field to always be that common column, no matter which CSV you're referencing (so in the example above, that would be Site Name). Does that make sense? 

    0
    Comment actions Permalink
  • Avatar
    Keith McQuait

    Wow! I don't know how or why, but it works. My feeble mind is having a hard time processing this one. lol.

    I thought this was the answer to Cynthia's question but now I went back and re-read it all over again, and you had shared this already and she said it wasn't the answer..

    bummer..

    Sara, you're still the best! The GoCanvas Community would be lost without you!

    0
    Comment actions Permalink
  • Avatar
    Sara Kaplow, Community Manager

    This is one that's hard, I think, conceptually, but when you walk through it, it becomes clear (and I totally had to walk through it again to remind myself of exactly what needed to be set where). 

    I do think this ended up being the solution for Cynthia (Kevin from our Support Team reached out to try to help), but again, it's so hard to get it without seeing it (and I clearly didn't explain it well enough originally). 

    Glad this is working for you! Thanks as always for pitching in and helping out, Keith!

    0
    Comment actions Permalink
  • Avatar
    Ashley Lincoln

    Sara Kaplow, Community Manager - Can you tell me if this works for loop screens?  I am struggling with the concept.  In my app the main reference field is selected in screen A and drives selections for the next screen, a loop.  In the loop I need to display additional fields based on the reference field selected in screen A but from a different file because of the 15 field limitations, but I am not sure how to do this while using the new sheet that I set up.  It seems they would stay independent.  Any help would be so appreciated! 

    0
    Comment actions Permalink
  • Avatar
    Sara Kaplow, Community Manager

    Hi Ashley,

    This does work for loops. In that case, you'd almost certainly want your key field to be the one that's repeated in both files, and then you use that as the Reference Field. Because the value will be the same for both, it'll pull the rest of that info as expected. 

    Here's what that looks like. I set my key field to be column 1, which is in both files: 

    Sorry for the boring data, but you can see that column 1 is in both files: 

    Now I can pull from either of those files based on that one column. Here's what that looks like in the builder. 

    To pull from column 10, I reference column 1 from the first file:

    To reference column 28, I populate the field with the second file, but reference the field in key field; because those values are identical, it works:

    Here's what it looks like on mobile: 

    Is that roughly what you were going for? 

    0
    Comment actions Permalink

Please sign in to leave a comment.