Question

How can list content of DB column in Forms?

Forms

Updated March 26, 2021

asked on May 15, 2015

Is there a way to capture entire content of one column from SQL DB within Forms page and create a result view on the form page?

We have a client that wants to use the form to get some reporting from SQL DB. We have three columns, two columns used to bring list of item into third SQL Column (show on form field). On the form we have no problem and we have the values show up in drop down field. We like to out put all the values of drop down field into form as an output result?

How can I achieve this?

Thanks

0 0

Answers

APPROVED ANSWER

replied on May 19, 2015 • Show version history

Here's an example:

This is what my SQL table looks like

And here is what my form looks like in the editor

The City drop down field uses the CSS class citylist. The Client drop down field is q3/Field3. The All Clients input uses the CSS class allclients

Here is how the lookup is configured. It's fairly standard

Here is the Javascript I'm using

$(document).ready(function () {
  
  $('#q3').addClass('hidden');
  
  $('.citylist select').blur(function() {
    var x = document.getElementById("Field3");
    var txt = x.options[1].text;
    var i;
    for (i = 2; i < x.length; i++) {
      txt = txt + ", " + x.options[i].text;
    }
    $('.allclients input').val(txt);
  });

});

The Javascript just takes the lookup values from the Client drop down list and appends them together into a string. Note that the assumption is that options[0] is blank so that's why I start with options[1] and then the for loop starts at i=2. This just makes for a cleaner display of the txt string with comma separators.

The end result would look something like

2 0

replied on October 18, 2017

@████████

How would you adjust the JavaScript so that the Custom HTML textarea would change size when a user is typing or pasting in text, please?

0 0

replied on October 20, 2017

There is another post in this same thread about doing this for custom HTML text areas. See https://answers.laserfiche.com/questions/77292/How-can-list-content-of-DB-column-in-Forms#77500

0 0

SELECTED ANSWER

replied on May 19, 2015 • Show version history

If you want to use custom HTML and have a text area where the height adjusts automatically, then you can use the following Javascript

$(document).ready(function () {
  
  $('#q3').addClass('hidden');
  
  $('.citylist select').blur(function() {
    var x = document.getElementById("Field3");
    var txt = x.options[1].text;
    var i;
    for (i = 2; i < x.length; i++) {
      txt = txt + ", " + x.options[i].text;
    }
    $('.allclientstextarea textarea').val(txt);
    $('.allclientstextarea textarea').trigger('change');
  });
  
  function h(e) {
    $(e).css({'height':'auto','overflow-y':'hidden'}).height(e.scrollHeight);
  }
  
  $('.allclientstextarea textarea').each(function () {
    h(this);
  }).on('lookup change', function () {
    h(this);
  });

});

The form itself is basically the same, but instead of using a separate single line or multiline field to store the client names, use a custom HTML field with something like

<textarea rows="1" cols="50"></textarea>

and give it the CSS class name allclientstextarea

Now your form should look like the following where you can see the text area height automatically changing

2 0

Replies

replied on May 18, 2015

Hi there,

Can you give me a screenshot/sketch to show what you try to achieve?

0 0

replied on May 18, 2015

Hello Abby

Basically, selecting City and the Question field will return list of client names from the data base. We need the entire list for the client name that gets returned (on this example we need all the names from client one to client six to be listed in the form). Client name field does not need to be drop down list. It can be any type as long as we can have all the values visible in the form that gets submitted. The actual form can return around hundreds or names.

0 0

replied on May 19, 2015

Hi there,

Thanks for the image. What does this part mean? "We like to out put all the values of drop down field into form as an output result? "

0 0

replied on May 19, 2015

We just need all the values to be listed from Client Names. It does not need to be a drop down field. Right now If I submit the form I will only have client one (as highlighted) listed in the PDF form that gets saved to repository. When I submit this form to the repository we want all the names from client one to Six to be on the PDF document.

we don't want to select anything from the client name list we want entire list to be the result of first two selections.

0 0

replied on May 19, 2015

Hi there

In this case, you can use a multi-line field in the table column and some customized javascript to achieve this.

Get the results set in dropdown, and fill the value into a Multi-line field.

1 0

replied on May 19, 2015

Hi Abby

That would work for me. I am not a developer, but I can work with an example java script. Could you provide me with a Java statement?

0 0

replied on May 19, 2015

Thanks Guys

It tested Alex's statement on my VM and it works fine. The actual client list will be large between 500 and 1000 and with single line field the name will be hidden on the page . I replaced single line text field with multi-line field but it didn't out put the result. I was using the same name and CSS class information as for single line (allclients).

The ideal would be to input the result in custom html box so we can all client names on the form.

0 0

replied on May 19, 2015

If you want the clients to be displayed in a multiline field, then replace

$('.allclients input').val(txt);

with

$('.allclients textarea').val(txt);

Make sure that your multiline field is using the CSS class allclients

0 0

SELECTED ANSWER

replied on May 19, 2015 • Show version history

If you want to use custom HTML and have a text area where the height adjusts automatically, then you can use the following Javascript

$(document).ready(function () {
  
  $('#q3').addClass('hidden');
  
  $('.citylist select').blur(function() {
    var x = document.getElementById("Field3");
    var txt = x.options[1].text;
    var i;
    for (i = 2; i < x.length; i++) {
      txt = txt + ", " + x.options[i].text;
    }
    $('.allclientstextarea textarea').val(txt);
    $('.allclientstextarea textarea').trigger('change');
  });
  
  function h(e) {
    $(e).css({'height':'auto','overflow-y':'hidden'}).height(e.scrollHeight);
  }
  
  $('.allclientstextarea textarea').each(function () {
    h(this);
  }).on('lookup change', function () {
    h(this);
  });

});

The form itself is basically the same, but instead of using a separate single line or multiline field to store the client names, use a custom HTML field with something like

<textarea rows="1" cols="50"></textarea>

and give it the CSS class name allclientstextarea

Now your form should look like the following where you can see the text area height automatically changing

2 0

replied on March 26, 2021

Alexander, wondering you could assist as I'm doing a similar task using Departments and Positions but these fields are in a collection where multiple departments can be added.

0 0

replied on May 19, 2015

Perfect Aelx, you answered all my questions. I was able to run all three formts (Single line field, Multi-line field and Html output). I like the HTML format.

Excellent response, Thanks for you help

0 0

You are not allowed to follow up in this post.

Question

Question

How can list content of DB column in Forms?

Answers

Replies

Sign in to reply to this post.