Pulling a hubdb column in as a choice dropdown

[ParkerShort]

I've been playing around with the capabilities of HubDB and it's pretty exciting. However, I'm trying to see if I can expand on the team directory idea in one very specific way.

I'd like to make team member pages that leverage the database. My plan (until more functionality is added) is to be able to have a choice dropdown on the team member page that would let me select a team member's name. The name would just be a cell in the database. So ideally, I'd be able to something like this:

Pull full names from database

store in array/variable

Use that array/variable in the choice dropdown, like so

{% choice "Employee" label='Choose employee',  choices=employee_name  export_to_template_context=True %}

Then I'd be able to pick whose page I'm creating from that dropdown, and let the rest of it populate based on that one choice. 

Currently, it looks like there's not a good way to get everyone's names out of HubDB except from within a for loop, which then means I can't call that variable later because it's out of scope. However, it's moot because you can't call an array as a choice dropdown. If it isn't predefined, there's not a way to use it as the choice.

So, TL;DR:

Can I access everything in one HubDB column outside of a for loop?

If not, can I transfer the variable value from inside to outside the loop?

Also, can I use that variable/information as a choice dropdown?

[grantfoster]

has anyone thought about this recently? currently having to api call, and then painstakingly format my hubdb row names and id's using regex. and I have to do this like every time my marketers add new rows to the db.

[matt_scott]

Update:
Hubspot have now added a new field in modules just for this use case as of the 3rd of Feb
https://developers.hubspot.com/changelog/now-available-hubdb-row-crm-object-fields

Old:
I managed to get it working with key value pairs so i could use the id of the row, I just want to do it in a custom module now

{% macro rowKeyValues() %}[{% for row in hubdb_table_rows( 666 ) %}["{{ row.hs_id }}", "{{ row.name }}"]{% if !loop.last %},{% endif %}{% endfor %}]{% endmacro %}
{% set keyValues = rowKeyValues() %}
{% choice "hubdbRow" label='Choose Row', export_to_template_context=True, value='', choices={{ keyValues }} %}

Note the choices = {{variable}} has to go at the end as it breaks because there are no quotes around it

[KOVIKO]

Does anyone have any ideas as to how we can utilize the module field in a template with 

export_to_template_context?

[Brandsensations]

Exactly what I've been searching for. Thanks!

[boulter]

Hi Parker - quick clarifying question: where do you plan to use this? In a custom module or on a page? The choice tag only works inside the editor to let the page author choose a value. It won't render on a public page.

Theoretically, you could get the list of names for use in a loop using a filter like this

        {% set names = hubdb_table_rows(TABLE_ID)|attr('name') %}

But that doesn't currently work. I'm looking at why.

Jeff

[AminWejebe]

¿Has anyone found a solution to get this working on a module yet?

[ParkerShort]

Hey Jeff,

To clarify, the choice dropdown would be in the editor. So ideally I have a list of employees:

Steve

Bob

Sally

John

And they would all be set up in the database with other information about them.

Then when I go in to create a page for Steve, I could just have a choice dropdown in the editor, and could have the rest of the information from the database fill in the page. It'd give me one place to keep employee information up to date and then it could update on both the listing and employee page at the same time.

I played around with your suggestion. Looks like you can't do the filter until you call the variable. I was able to make some progress doing this:

{% set table_info = hubdb_table(XXXXXX) %}
{{ table_info|attr('columns')}}

But then that spits out all of the attributes about each column (name, type, ID, options, etc.). I'm still trying to get the info in one columns (not the information about a column) and then use that to prepopulate my choice dropdown. This might be progress.

[boulter]

Hey Parker, 

Sorry for delay. I had a little vacation in there. 🙂

It looks like you want to use map(), not attr() as I previously suggested. This works for me to get a list of the values of a single column:

{% set rows = hubdb_table_rows(XXXXXX) %}
{% set names = rows|map(attribute='name') %}

You could append additional filters like 'sort' and 'unique' if you'd like.

This doesn't yet solve the problem of getting these into a choice value in the editor. I'll work on that next.

[boulter]

I got the choice working. Just put the list of employees as the choices parameter.

{% set rows = hubdb_table_rows(XXXXXX) %}
{% set names = rows|map(attribute='name') %}
{% choice "Employee" label='Choose employee', choices={{ names }}, export_to_template_context=True %}

Let me know if you have any problems with that.

[ParkerShort]

Just now getting around to this! Tested it out and I get an error that says "InterpretException: Invalid Choices List : [ Choice 1, Choice 2]"

Where Choice 1 and Choice 2 are the exact things I want as choices. Any idea what I should try to get it to work? Would the spaces be throwing it off?

[joneichler]

I am also trying to get this to work but i am getting an error when trying to publish the template, says it can not validate the template at this time.