Pulldata in multiple surveys from single source

1346
12
Jump to solution
11-14-2019 11:30 AM
BRYANHERRON
New Contributor III

All the instructions I've found so far say that the .csv must be put into the media folder.... So that .csv is only good for a single survey.

Is there any way to force all my surveys to pulldata from a single source?

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
BRYANHERRON
New Contributor III

YES WE CAN!!!!! 

Just did a test run with linked cells from a single source and it works!!!!

View solution in original post

0 Kudos
12 Replies
DougBrowning
MVP Esteemed Contributor

Not that I have seen. 

My idea is to have a master lookup form (have a form that just has a bunch of choices, external_choices, or csv files with it).  Then the pulldata has a itemid in it that points to this master form.  Seems like a way to do it without any new tech needed.  

I have an Observes list that varies throughout the year as crews come on and leave.  Right now I have to update the same list in 9 different forms.  Having a Company X lists form would be cool.

BRYANHERRON
New Contributor III

Great idea.

I wonder if we can link cells in the XLSForm choices tab (or custom choices tab) to a master excel file?

0 Kudos
BRYANHERRON
New Contributor III

YES WE CAN!!!!! 

Just did a test run with linked cells from a single source and it works!!!!

0 Kudos
CPoynter
Occasional Contributor III

Hi Bryan,

Can you outline the workflow steps you used to make this happen please.

Regards,

Craig

0 Kudos
BRYANHERRON
New Contributor III

Certainly, If you have data, let's say its something simple like the names of your field crew, that needs to be included in multiple forms and has the potential to change from time to time.

  • Put the names into an excel file that will be your master for all data used in multiple forms.
  • CREATE A BORDER - It would be a good idea to allow for additional employee names, so if you currently have 20 people in the field select 30 rows and allow for additional columns for their phone numbers, addresses, etc, in case you decide to use that, or any other, type of employee data in some way down the road, and put a heavy border around those cells. This is just to create a boundary that you know not to place any non-related data in that area of the spreadsheet.
  • So now maybe you have 5 columns and 30 rows protected in your XLS, go to your choices tab in your XLSForm and link those cells in the master XLS to the XLSForm. This part is a bit of a pain to me, because its clunky and I'm sure there's a better way. If you know how to do the links, just ignore the rest of this, unless you can tell me how to do it better.
  • Go to the the top left cell of where you are going to start linking the master xls, in the choices tab
  • Select that cell
  • Type the = sign
  • Select the cell in the master XLS that you want linked
  • Make the XLSForm active again
  • Hit a return and the data from the XLS should be linked and appear.
  • Select the linked cell, select the little + and drag it down for a total of 30 cells to link all of the protected cells in the master XLS.
  • Here's where it gets dumb, because you would think that at this point you could simply highlight the cell with the link in it and drag it down the column to create the rest of the links in that column. When I do this it just copies the A1 link to the other cells and I get a column full of links to A1. So I change them all to A2, A3, A4..... A30. This is still much faster than linking each cell.
  • Then highlight those 30 cells and drag them 4 columns to the right.
  • So now in the B column all of your links are A1-A30, change them to B1-B30
  • Do the same for the remaining columns.

If there is a way to do this more efficiently, please let me know.

CPoynter
Occasional Contributor III

Thank you Bryan.

0 Kudos
DougBrowning
MVP Esteemed Contributor

This will work when in Excel but you would still have to open, refresh, then republish each form.

You can even link excel to SQL directly.  But again this just changes your excel on your computer.  Still got to get it up to 123.  Still have to open all your forms and republish.

0 Kudos
BRYANHERRON
New Contributor III

But only the existing forms, anything new after building the master would a cinch.

0 Kudos
BlytheSpendlove2
New Contributor III

You can also pull data from a feature server with some code in a calculation field  

You will just have to add an extensions folder to your survey folder with a file with the appropriate JavaScript functions in it.

0 Kudos