How to add reference data for more than 15 columns

Answered

Comments

22 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
  • Avatar
    Sara Kaplow, Community Manager

    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
  • Avatar
    Sara Kaplow, Community Manager

    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
  • Avatar
    Sara Kaplow, Community Manager

    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

    @... - 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
  • Avatar
    Andy Middleton

    Hi,

    When uploading the reference data files, what do you call them? I'm having issues as I can't have the same name for reference data files.

    Help!

    0
    Comment actions Permalink
  • Avatar
    GoCanvas Community Manager

    Hey Andy,

    Is there a particular reason why you need your files to be the same name? Because if they are exactly the same file, only updated with new rows or new columns, then you only need to overwrite the existing file by using the Edit button and reuploading the file. This will update the data that the app will pull from automatically when making the submission. This is the best way to keep your files organized and save you time! If you reupload the same .csv as a new file every time the file changes, then you will be doubling the work of reattaching/re-mapping that file to your app which will increase the editing version and be a big headache. By simply editing the existing file that is already attached to your app(s), that will update everything across your entire account. Below is a Help Center article covering this:

    How to update reference data with most recent file version: https://help.gocanvas.com/hc/en-us/articles/115006847328-How-to-update-Reference-Data
     
    If there is another reason you are running into this issue, please elaborate and I am happy to help!
     
     
    0
    Comment actions Permalink
  • Avatar
    Andy Middleton

    Hi Lauren,

    I just need to know what to call the files so they still reference each other?

    Or am I missing something in the previous steps?

    0
    Comment actions Permalink
  • Avatar
    GoCanvas Community Manager

    Hey Andy,

    Thank you for clarifying! For this, the files do not need to be the same name. The best way to connect two files is to have one column of data that is the same across both files. Sara shows this above where she has her "Column 1" in both her files, where the second file picks up "Column 16" in the second column of the file. That way, there is common data between the two files that can be used to connect the information in the submission. 

    0
    Comment actions Permalink
  • Avatar
    Andy Middleton

    Lauren,

    I'm sorry, but I'm struggling to get this set up. I have common data but it's not letting me reference between the files.

    Who can i reach out to in order to get some proper help on this? This 15 column limit is a real joke, this needs fixing.

    0
    Comment actions Permalink
  • Avatar
    GoCanvas Community Manager

    Hey Andy,

    I understand your frustration, this is typically an issue with how the fields are being mapped in the app. In Sara's example above, the common column is the first column of data. This works best when this first column is the parent field for the rest of the fields to populate. For example, if the first column was the customer's name and everything that followed, i.e. address, phone, email, etc., was populated by the name of the customer.

    If you are using dependent reference data, than there is dependency that needs to be taken into account. If the first column does not populate all the following columns and there is a branching point in the data, the common column needs to be a column that is connected in the map to the following columns. For example, if it is a parts list and at some point the parts branch off into names of the part and details about that part are directly mapped to the field containing the part name, the part name column would be a good common column because the next column in the second file is mapped to populate based on the part name, not on the fields that precede the part name.

    I know how confusing that may sound so I have reached out to your account manager and created a ticket for support. Once support knows the app and fields that need mapped to the reference data, they will be able to tell you where the disconnection is occurring. 

    0
    Comment actions Permalink
  • Avatar
    Andy Middleton

    Hi Lauren,

    I think that makes sense, thank you for raising the ticket.

    Hopefully it can be rectified easily.

    As a side note, not having a 15 column limit would remove the need for this clunky workaround! ;)

     

    Andy

    0
    Comment actions Permalink
  • Avatar
    Nathan H

    Hi Andy,

    There's an existing feature request for more than 15 columns if you wanted to vote it up:

    https://help.gocanvas.com/hc/en-us/community/posts/360019815913-Allow-more-than-15-columns-fields-for-reference-data

     

    0
    Comment actions Permalink
  • Avatar
    Andy Middleton

    Cheers Nathan. I'm on that like a seagull on chips

    1
    Comment actions Permalink
  • Avatar
    GoCanvas Community Manager

    Hey ya'll,

    Thanks for linking this Nathan! And I totally agree, Andy. We have heard the complaints and appreciated the upvotes on the post and have definitely taken this into consideration for future improvement. 

    1
    Comment actions Permalink

Please sign in to leave a comment.