text box only shows first row of result set from sql query - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

text box only shows first row of result set from sql query

Last post 08-03-2021 03:58 PM by lou d. 22 replies.
Page 1 of 2 (23 items) 1 2 Next >
Sort Posts: Previous Next
  • 07-28-2021 01:49 PM

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    text box only shows first row of result set from sql query

    Hello, I am totally new to Infopath. I am using it to retrieve data from sql server. My result set for one field consists of more than one row, but the text box i display the results in is only showing the first row. Is it possible to get the additional records to show in the box? The maximum number or records would be less than 10 rows. Thanks! Lou
    Filed under:
  • 07-28-2021 05:04 PM In reply to

    Re: text box only shows first row of result set from sql query

    I would just use a repeating section for this - is there a reason all the data needs to be in the same text box? I mean, you could use double eval for this - see attached sample. Right click the link in the heading, save the file locally. Right click the saved file, and select design to open in design mode, then you can preview.
    Hilary Stoupa

  • 07-28-2021 08:04 PM In reply to

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    Re: text box only shows first row of result set from sql query

    Thanks for the info. This form is running a sql query and returning the result set to the form. I do not need to enter rows onto the form; i just need to display the sql results. There are a few fields that have more than one record and it would be easiest (if possible) to display the multiple rows (never more than 8) in a text box. Do I add the repeating section just to host this text box? I will research repeating section and double eval, as I don't know what they are. Do I need to use both features to display the multiple rows?
  • 07-29-2021 08:06 AM In reply to

    Re: text box only shows first row of result set from sql query

    So - in my sample form, I just used a repeating table to show you how a double eval works. In your form, the formula would change - feel free to attach your form to a reply if you need a hand. You can attach the form under the options tab in a reply. Does your returned data only have one column? Or are there multiple columns? Personally, I'd just drag the repeating group from the SQL data connection onto the form as a repeating section or table, and change the controls to calculated values (so the user doesn't think they can change the data). I'd rather see tabular data in a table format.
    Hilary Stoupa

  • 07-29-2021 09:38 AM In reply to

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    Re: text box only shows first row of result set from sql query

    Thanks. The result set would just have one column. There are other fields that will have multiple rows, but I was hoping to figure it out for this one and then I'll do the others. I added a repeating section towards the bottom of the Modify Metric view to test this out, and dragged the field from the SQL data connection onto the form. It did show the 5 records, but it also showed hundreds of blank records (with the field label for each one), so the form stretched out lengthwise big time. I therefore deleted the repeating section for now. I was hoping to have this look like a simple text box with the 5 lines in it. I've attached the form. The field that has the multiple rows in this case is in the SQL Data connection called 'Secondary Data Source (Secondary)', and the field name is metric_type_name. The view I am trying to add it to is called Modify Metric. Thanks for any assistance. Lou
  • 07-29-2021 04:12 PM In reply to

    Re: text box only shows first row of result set from sql query

    I put a repeating table with a single column, a calculated value, and the header and borders removed at the bottom of the view in this copy of your form. I also included some info on what I did. This is the approach I'd take rather than using a formula.
    Hilary Stoupa

  • 07-29-2021 07:09 PM In reply to

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    Re: text box only shows first row of result set from sql query

    Thank you. Your solution brings repeats the records hundreds of times and also brings back hundreds of blank records. I modified your .xsn to filter the formula on the metric_code from the data connection, which is field32 in my group. This does retrieve the five rows I am expecting, but it creates a few blank values before the first record, and hundreds of blank records below the fifth row, making the form VERY long. I've attached a screen shot to show you if that helps. I've also attached your .xsn file with my change. Many thanks!. Lou
  • 08-02-2021 07:54 AM In reply to

    Re: text box only shows first row of result set from sql query

    On the repeating table, add conditional formatting to hide if the field is blank. See attached.
    Hilary Stoupa

  • 08-02-2021 11:33 AM In reply to

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    Re: text box only shows first row of result set from sql query

    Thanks but I downloaded your attachement and tried to test it out. When I click on preview, my data connections to SQL server no longer work. I closed out of your attachment and opened my version, and it no longer works either. I don't get any error messages, but when I click on Preview I do get the prompt to connect to the database, but I no longer get any records back and I don't get an error message. Any idea why? Thanks! Lou
  • 08-02-2021 11:43 AM In reply to

    Re: text box only shows first row of result set from sql query

    Well - I took your copy and changed it, just to add conditional formatting. Here's the copy I posted the other day with the same format change - I haven't walked through the data connection, so I'm not sure what would have changed there.
    Hilary Stoupa

  • 08-02-2021 11:49 AM In reply to

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    Re: text box only shows first row of result set from sql query

    I think I see where you put the conditional formatting. It's a rule called 'Hide for Blank'. Note that I do not want to hide the control. the repeating table you added does bring back the five rows I need, but the table shows blank records before the first value, and hundreds of blank records after the last value. If you look at the screen shot I attached earlier, you can see what I'm referring to. Thanks for any assistance.
  • 08-02-2021 04:58 PM In reply to

    Re: text box only shows first row of result set from sql query

    I don't see any screenshots, sorry, just the form you attached. The rule to hide the row was to hide anything where the value was blank - which is what you are saying you want hidden.
    Hilary Stoupa

  • 08-02-2021 06:06 PM In reply to

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    Re: text box only shows first row of result set from sql query

    Can I attach .png files so you can see the screenshot I was referring to? I attached it earlier but it looks like you only saw my .xsn file. I'm re-attaching the .png file here, where you can see I get hundreds of blank records; a few before the first record and many after the last record. The form prompts the user to enter in an ID from a data connection, and based on the ID selected, the repeating table should show the values that correspond to the ID. It looks like the rule you created is intended to hide the blank records, but they still show up. My intent is to just hide the blank records that appear with the populated ones, but the control should always be shown. This field will always be populated with at least one row. Thanks! Lou
  • 08-03-2021 08:03 AM In reply to

    Re: text box only shows first row of result set from sql query

    I wonder if the field is just not returned if it is blank - I know SharePoint does that, but don't know about SQL. An easy way to test this would be to change the conditional formatting to color the row instead of hide it. Then add another formatting rule to turn it a different color if it is not present.... I suppose it could also contain a blank char, and not be blank. Can you directly query the database? What do the blank metric type values look like in the actual data?
    Hilary Stoupa

  • 08-03-2021 09:19 AM In reply to

    • lou d
    • Top 500 Contributor
    • Joined on 07-27-2021
    • Posts 29

    Re: text box only shows first row of result set from sql query

    Thanks. I was able to get it to work (see the screenshot I attached). However, the repeating table causes the form to now have a large gap between the last row of the repeating table and the next field below it. It's not that big of a gap, but I would like to have the next field (i.e, Reporting Frequency) to show up close to the repeating table. And in some cases depending on what I select in a dropdown field above it, it creates a few (or many) blank records before the first row. Is it possible to remove these leading and trailing blanks? Thanks again!
Page 1 of 2 (23 items) 1 2 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.