Query Custom Fields

Jimmy Coulson April 17, 2019
ShoutOut to all my techie friends that have ever used Trello.

Have you ever setup a way to query the cards of one board using the custom fields of a card on another board as input for the query?

I’m thinking if possible some heavy automation is required.

1 comment

Iain Dooley
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 17, 2019

@Jimmy Coulson your heavy automation requirement is no match for the Trellinator!

Here's a command that takes what you type into a custom field called "Query" and uses it as a search term to find all open cards in all boards where Trellinator is a member, then add each of those cards as a checklist item to a "Results" checklist on the trigger card:

https://gist.github.com/iaindooley/0363c9a2870ba2c582328963a3c612f6

You can see how to set up Trellinator here:

https://community.atlassian.com/t5/Marketplace-Apps-articles/Introducing-Trellinator-Automate-Trello-with-Google-Apps-Script/ba-p/925271

Jimmy Coulson April 18, 2019

I step through the initial HelloWorld setup of Trellinator and was unable to get it to work. I followed the instructions in the description and walkthrough you provided but it ultimately didn't output to my board. Has anything changed since you wrote that initial tutorial?

 

Thank you for your reply btw.

Iain Dooley
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 19, 2019

@Jimmy Coulson Nope, all still the same, however are you referring to this Hello World?

https://www.theprocedurepeople.com/trellinator-automate-trello/docs/trellinator_hello_world.pdf

That's considerably more complicated than the Quick Start guide here:

https://community.atlassian.com/t5/Marketplace-Apps-articles/Introducing-Trellinator-Automate-Trello-with-Google-Apps-Script/ba-p/925271

The most common mistake in the quick start setup is to have Trellinator running as the same user you're making comments as. When you make a comment as Trellinator it gets ignored (otherwise Trellinator would infinitely add comments) so you need to invite a separate user to the board and post a comment from that account, to see Trellinator react.

You can also share your Sheet with iain@benkoboard.com and I can take a look at the set up to help you diagnose the issue if that doesn't work :)

Jimmy Coulson April 19, 2019

@Iain Dooley 

I went ahead and successful tested HelloWorld and it worked perfectly and much appreciated.

 

After trying to run the example code I couldn't seem to get it to work.

 

I created my Test Board to look like this:

Card Query.jpgSecond Card.jpgTrellinator Code.jpgTrello Test Board.jpgTrello Test Card.jpg

Iain Dooley
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 19, 2019

@Jimmy Coulson okay just want to start by making sure you have installed the searchCardsOnBoardsUsingCustomFieldsAsInput function into the board configuration sheet the same as helloWorld right?

Also, note that Trello search doesn't search custom field values. The filter does, but this is an interface element, you can't filter cards using the API.

If you wanted to determine if cards on a board contained custom field values you'd have to loop through all the cards and check the custom field values which would take longer, but here is the code:

https://gist.github.com/iaindooley/1868e392b9dfb3d1ab7544617c45ca03

Note that if you had lots of boards with lots of cards this might time out... if you had to do it across lots of boards, one solution would be to schedule each board's search function using the ExecutionQueue something like this:

https://gist.github.com/iaindooley/dba84f2792fba2ee97dd8bbb2b916dc5

Or if you were only doing this within a single board you wouldn't need to loop through all boards Trellinator was on, so it would look like this:

https://gist.github.com/iaindooley/8c69a0822c048bd5d062de510341ded2

Jimmy Coulson April 22, 2019

Iain,

Again thank you for walking me through this.

I have my test board setup and helloWorld worked. I pasted the searchBasedOnCustomFields function on the command.gs along with the helloWorld.

I published the app and then ran Trellinator Utilities.

I then created another board and made sure that Trellinator was added to it. I made a Custom Field named "Hello" with the value of "hello"

 

When I went back to the original Test Board and created a field named "Query" and inputted "hello" nothing was returned.

James

Jimmy Coulson April 22, 2019

I've added the function searchBasedOnCustomFields to both boards of the Copy of Trellinator Quick Start by the Procedure People spreadsheet.
I've saved and re-ran it. Function Call Board 1.jpgFunction Call Board 2.jpg

Iain Dooley
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 22, 2019

@Jimmy Coulson the function only needs to be installed in the tab for the board on which the change in custom fields occurs. Trellinator needs access to all the boards it will search upon, but you don't need to install the function on those boards.

Also note that you don't need to run the initialise function each time you publish a new version of the web app -- the URL doesn't change each time.

When you make the change to the custom field value, you should see an entry appear in the Info Log tab for that notification. This will have some log messages in it, in JSON format. I use this site to view them:

http://jsonviewer.stack.hu

If there are any errors in there, you'll see them, but most commonly what you'll see is that an expected exception was caught. The messages you'll see will be from here:

https://www.theprocedurepeople.com/trellinator-automate-trello/docs/trellinator-libs_Notification.js.html#line208

Either "No custom field was changed" or "The updated custom field was not named BLAH". But if a custom field was changed, and it was named Query, and the function executed correctly but returned no results, you'll just see no additional messages there.

If an unexpected exception occurs you'll also see an entry to retry in the ExecutionQueue tab, and it will retry every 10 minutes. This will usually be a javascript error or something like a Trello API connection problem.

This can be useful because if you copy the JSON out of the execution queue and put it into your code, you can call it directly using that exact notification like this:

https://gist.github.com/iaindooley/997924efc2e2166cef1e33a122305f1a

Otherwise you can use the Trellinator.log() method to inject messages into that for debugging purposes.

Note that I just realised that the comparison for field values was case sensitive, this updated version of the code uses toLowerCase() but I'm unsure how this will impact non-text field values:

https://gist.github.com/iaindooley/1868e392b9dfb3d1ab7544617c45ca03

Also I'm unable to really debug the code very effectively without access to the project. I tested it out in my own setup and it worked. Beyond that I'd recommend getting a Google Apps Script developer from Upwork to install/debug this for you if it's not in your wheelhouse. Alternatively I can manage that for you, there's a link in my profile.

Comment

Log in or Sign up to comment
TAGS
AUG Leaders

Atlassian Community Events