CMS Development

tmcmillan99
Contributor | Elite Partner
Contributor | Elite Partner

HubDB search results in template project - Best approach?

SOLVE

I am about to jump into a project that is uncharted territory for me and wanted to reach out to the community to determine the best and proper approach to this project.

 

Project-

Client wants a "Find a Dealer" search function. End user will input a zip code or state to find their appropriate dealer. The results will display the dealer name, logo, website, and phone. Client has provided two spreadsheets. One contains the zip codes and states with the appropriate dealers associated with that info. Some zip codes/states will have more than one dealer which will be listed in order of preference based on how they are listed in the spreadsheet. The second spreadsheet contains general account information about each dealer that would be part of the results displayed. Both spreadsheets contain a customer id column.

 

What is the best way to approach this type of project?

My initial thought was to create HubDB tables that could be used for the search query. Although Hubspot is not the client's source of truth for dealer account info, most of them are in Hubspot and I believe the client would be willing to make sure their info is maintained in the company records. I wasn't sure if pulling the dealer info from the company records was a better/easier option than the HubDB table.

 

If the search input field is on one page, how do I display the search results on a different page?

Although I have used basic Hubl code in modules, this will be the first time I do something this complex and utilize search queries. I was going to lean on the Real Estate Listing with HubDB example as a general outline to follow.

 

I would appreciate any suggestions on how I should approach/organize this type of project.

Thanks,

Terry McMillan

0 Upvotes
1 Accepted solution
piersg
Solution
Key Advisor

HubDB search results in template project - Best approach?

SOLVE

You could have one table that includes the order preference info (which is really the only thing that spreadsheet is useful for). So each row is one dealer and includes their zip code and their ranking for that zip code, then when a user searches a zip code you serve those results, sorted by rank.

 

Or you do two HubDB tables, when a user searches a zip code you get all the dealer ids for that zip from the first DB, then look up those ids from the second DB to get dealer info in the same order they were in in the first DB.

 

I think the second method would require JS fetch/AJAX. However, the first could probably be done more simply by adding a data attribute to each dealer listing for zip and setting order with flex order like the below. Then as a user types or when they press enter on the zip code input field, use JS to find what they've typed in the the zip data attribute (display: none all the non-matching items, display: flex the matching items), and they'll be sorted by rank because of the order in the style attribute. Or set the rank in another data attribute e.g. data-rank="{{row.rank}}" and do the arranging in the JS.

{% for row in table %}
  <div class="dealer" data-zip="{{row.zip}}" style="order: {% if row.rank %}{{row.rank}}{% else %}1000{% endif %}">
    <h3>{{row.name}}</h3>
    <p>{{row.phone}}</p>
    <p>{{row.website}}</p>
  </div>
{% endfor %}

 

View solution in original post

8 Replies 8
tmcmillan99
Contributor | Elite Partner
Contributor | Elite Partner

HubDB search results in template project - Best approach?

SOLVE

@piersg 

Thanks for this suggestion!! I think keeping it to one table will simplify things and I love the suggestion of including a rank in conjuction with css to list the dealers in order of preference. I will test this out with a couple of dealers in order to get the queries sorted and have the search working correctly before I finish building out the table with the rest of the data. 

Thanks,

Terry

tmcmillan99
Contributor | Elite Partner
Contributor | Elite Partner

HubDB search results in template project - Best approach?

SOLVE

@remingtonbegg @piersg 

One last question...Would you recommend that I just have one HubDB table that contains all the info including the dealer info? Or should I have one table with the zipcodes, states, and dealer id or name and then one table with the additional dealer info needed such as name, phone, website, and logo url?  I am assuming that having 2 linked tables will add some complexity to the queries but maybe not.

 

Thanks,

Terry

0 Upvotes
piersg
Solution
Key Advisor

HubDB search results in template project - Best approach?

SOLVE

You could have one table that includes the order preference info (which is really the only thing that spreadsheet is useful for). So each row is one dealer and includes their zip code and their ranking for that zip code, then when a user searches a zip code you serve those results, sorted by rank.

 

Or you do two HubDB tables, when a user searches a zip code you get all the dealer ids for that zip from the first DB, then look up those ids from the second DB to get dealer info in the same order they were in in the first DB.

 

I think the second method would require JS fetch/AJAX. However, the first could probably be done more simply by adding a data attribute to each dealer listing for zip and setting order with flex order like the below. Then as a user types or when they press enter on the zip code input field, use JS to find what they've typed in the the zip data attribute (display: none all the non-matching items, display: flex the matching items), and they'll be sorted by rank because of the order in the style attribute. Or set the rank in another data attribute e.g. data-rank="{{row.rank}}" and do the arranging in the JS.

{% for row in table %}
  <div class="dealer" data-zip="{{row.zip}}" style="order: {% if row.rank %}{{row.rank}}{% else %}1000{% endif %}">
    <h3>{{row.name}}</h3>
    <p>{{row.phone}}</p>
    <p>{{row.website}}</p>
  </div>
{% endfor %}

 

tmcmillan99
Contributor | Elite Partner
Contributor | Elite Partner

HubDB search results in template project - Best approach?

SOLVE

@remingtonbegg @piersg 

Thanks so much for the responses. I appreciate you taking the time to offer suggestions. Fortunately, this project does not have to be the same type of functionality that you would find with a "store locator" search using a mileage radius. The client's spreadsheet includes all of the specific zipcodes and the state associated with each individual dealer. As a result, I just need to display the dealer's basic info if the search "contains" one of those zip codes or state. Since the client doesn't have access to custom objects, I will move forward with the option of importing csv files into HubDB and using Hubl queries for the search function. Hopefully I can pull this off. Nothing like learning new stuff on the fly and under pressure. Lol.  I know I can reach out to the awesome community if I run into issues.

 

Again, thanks for the suggestions and the willingness to help.

Terry

 

piersg
Key Advisor

HubDB search results in template project - Best approach?

SOLVE

@tmcmillan99 @remingtonbegg I don't think you need Custom Objects for this Terry. If you have a HubDB with all the dealer info, including location (if you can get location in latitude and longitude; there's a table here you can download as CSV or JSON to look up a zip code's lat and long to convert it), you can get the distance between the user's location (or a zip code they chose) and the nearest dealers by using the geo_distance HubL filter. There's a Hubspot guide to building something similar here.   

tmcmillan99
Contributor | Elite Partner
Contributor | Elite Partner

HubDB search results in template project - Best approach?

SOLVE

@dennisedson 

Thanks for the response and tagging a few others on the topic. I could use all the help I can get on this project. 

This client is on the Professional tier for all of their products. If I remember correctly, I think you need the Enterprise tier for access to custom objects.

 

Thanks,

Terry

 

0 Upvotes
remingtonbegg
Contributor | Elite Partner
Contributor | Elite Partner

HubDB search results in template project - Best approach?

SOLVE

 

Hello! So I dig into the stream of consciousness in the video. But couple limiting factors I see.

 

  1. There are over 40,000 zip codes in the USA. HubDB caps out at 10,000 rows. I would recommend using your dealers and having a comma-separated list of eligible zip codes for a dealer to show up.
  2. This is a great use case for custom objects. While it does require ENT it would open up a lot more functionality and make the development significantly easier.
  3. The other option is to build out a dictionary in json with your zip to company ID's and run through a filter. This is not efficient and the time loading the page could suffer, but depends on the requirements of the project.

 

Let me know if this helps any. 

dennisedson
HubSpot Product Team
HubSpot Product Team

HubDB search results in template project - Best approach?

SOLVE

@tmcmillan99 

This is a great question.  I wish I could follow you on this journey.

Out of curiosity, what tier(s) is the company on?  Do they have access to custom objects? 

@Kevin-C , @remingtonbegg , @stefen , @Anton , @piersg , @alyssamwilie, I know search is the question, but I feel like there is a lot more going on here for a discussion and I am curious what you all think