Pre-fill check boxes, radio buttons, list control, or multi-select with records from a database table

Get Help
,
1

I’ve got a use case where I though it should be easy to achieve, but I cannot quite seem to get there. I want to present a user with a list of items, and get a check box response for each item. Would look like this:

UserID: xxxxx

Area / Available
a / Y
b / N
c / N
d / Y

The “areas” are stored in a database table, and can be fetched once the User ID is filled in. The problem is that which areas need to be shown and the number of them can differ for each user.

Tried working with Populate Anything, but no matter what I try, it only populates the first area record it finds.

I searched Perks, and found “Auto List Field”, which sounded perfect figuring I could populate a field with the record count. Can’t figure out how to do that with populate anything (would be really nice if you could execute a SQL statement), nor can I get Populate Anything to fill in the fields (using Auto Field List on a list control, I placed a numeric field on the form, manually set it, would get the rows I wanted, but could not get them filled).

I also searched for snippets and found one that I thought was going to do the trick, but it flattened multiple records into a single field ( snippet-library/gp-populate-anything/gppa-populate-all-results.php at master · gravitywiz/snippet-library · GitHub ) rather than creating multiple rows.

Have hunted and hunted and come up with things that are close, but not quite what I need. I’m relatively new to GF, PHP, etc., so just may not be seeing the answer.

I did come up with a solution I think would work, but l would need the record count populated in a field. Since I can control the design of the table, I could number the records for each user with a “Row ID”. i.e.

User / Row ID /Area

123456 / 1 / a
123456 / 2 / b
123456 / 3 / c
123456 / 4 / d

456 / 1 / x
456 / 2 / y

On the form then, I would have a series of pre-defined list controls (say 20), populating each list field using the UserID and the row, and show only the ones needed for the user (Show this field if row<= Hidden Record Count). This would also allow me to have multiple columns by adding additional fields for the row. I need the Y/N “Available” for now, but in the future, there might be additional columns.

Anyone have any thoughts on how I might achieve this?

Thanks,

Jim.

2

Hi Jim,

If I understand your use case correctly, this should be possible using Populate Anything.

When populating a choice-based field, there are two types of population:

  • Populate Choices Dynamically
  • Populate Value Dynamically

In your case, as you want to populate the choices based on the user, you’ll need to use Populate Choices Dynamically. This will populate all the results based on your filters, with each result appearing as a separate choice.

If you have an active Gravity Perks license, we’ll be happy to dig in some more with your specific use case via support.

Best,

3

I thought so as well, but it always populates just the first entry and doesn’t do the rest.

and yes, I do have a GF license, so I will open a ticket.

Jim.

4

So this turned out to be two things, both known limitations with Populate Anything:

a:
”When populating from custom database tables, the primary key must be the first column of the database.”

While I did have my primary key as the first columns, it was a compound key (made up of two fields). The fix was to add an ID column with Autoincrement and designate that as the primary key. I then made a secondary unique index for my compound key (User ID / Area).

b:
”List fields can only be populated to/from other List fields.”

Since this is just the way it works and you cannot populate a list control from a database table (even with the Auto Perk), I’m going with a multi-select for the moment. If I do need multiple columns in the future, I am going to:

1. Add a “nth row number” field to the table. Numbering would start over at one for each group of records:

UserID / nth Row / area
123456 / 1 / a
123456 / 2 / b
123456 / 3 / c
123456 / 4 / d

456 / 1 / x
456 / 2 / y

2. Add a grid of pre-defined fields to form rows on the form. Each row of controls would populate filtering by the group (UserID) and the nth Row

3. Populate a number field with the count for the group by filtering on the group (User ID) and using the merge tag {count}:

4. only show rows that are valid by using the conditional check: row number <= Count Field.

I didn’t test this out, but I don’t foresee any problem in doing this. In some cases, it would not be ideal in that the number of records might exceed the number of pre-defined rows, but I could not see any other way of solving this at the moment, and in my case, the areas are pre-defined, so the maximum rows is known.

5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.