Substituting Multiple Strings in Airtable

Thanks to W_Vann_Hall over on the Airtable Forums, I was able to figure out the formula I needed. The task is this:

* In JotForm, people select book covers to indicate which book they like.
* A Zapier zap takes that submission and puts it in my Airtable.
* However, the data appears at a bad URL with https://jotform.com added to the front of it. Worthless for our purposes.
* So to change that info, I need to substitute the long junk URL to the name of the book.
* I created a new column and added this formula:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Name of Column — leave in brackets},’https://www.ilovemylibrary.com/images/little_red.jpg’,’Little Red Riding Hood, ‘),’https://www.ilovemylibrary.com/images/three_little_pigs.jpg’,’Three Little Pigs, ‘),’https://www.ilovemylibrary.com/images/cinderella.jpg’,’Cinderella, ‘)

* Output: Little Red Riding Hood, Three Little Pigs, Cinderella,
* Note that I added a comma and a space at the end of each item. I’m sure I could try to figure out how to check for any leftover items, so there isn’t an extra comma, but eh.

I’m sure there’s more work to be done and something will break, but I’m grateful to get at least this much figured out.

Under the Weather + Project Help

Since late January I’ve been under the weather. As such, I got an extremely last minute request (order!) from my doctor to forego attending ALA Midwinter. Thanks to the kind efforts of the LITA Board, I was able to attend the Board meetings by phone. My mind and energy has been redirected to the efforts of getting better this past month. Thus why things have things been so quiet around here. With any luck, I hope to be fully back in fighting shape any day now.

Meanwhile, the launch of the previous post’s project has been a roaring success. The Book Matchmaker has been so popular that I’m trying to find a solution to help them better manage it. My vision:

  • Easily tracks the submitter’s stated reading preferences.
  • Quickly show other times we’ve recommended a book to another reader. Thus, hopefully reducing staff time by seeing “oh, we said this book was great for Sci-Fi readers. Would this person appreciate it too?”
  • Help staff with planning out the next books to pull for that reader and when to do so.
  • Dead simple for staff to use.

Currently I’m thinking Airtable looks to be the best bet. Yet I’m not sure I can easily get the new submissions added automatically. I’ve tweeted at Airtable to see if they allow records to be added via email. Asana does which has spoiled me. If you have any further recommendations, let me know!

Re-launch Tomorrow

My lovely colleague took over a big project after one of our Assistant Directors left. She’s been polishing it for months now and tomorrow it goes live! I’m looking forward to discussing the work that went into bringing this service back online.

The most helpful thing for me was her interest and willingness to try her hand at Adobe Illustrator. She’s been working in InDesign for not too long and Illustrator is a whole other beast. By taking on this proactive role in her branding and publicity creation, she’s really owned this work. I’m happy to have helped when she needed me.

December 2017 Monthly Report

My new boss has asked that I write out a more descriptive version of what I did in the previous month. Aside from all the usual work (social media, digital signs), I had twenty separate projects in progress. What I liked best about this report is that it prompts me to follow up on the items. A few highlights are below.

I strike a pose on a frozen pond. These don’t exist back home!

Museum Passes

We had a 20.4% increase in museum pass reservations in December vs. November. We sent out a special email highlighting museums in December which may have caused that increase.

Increase in Hoopla

In December 2016, we didn’t send out an email highlighting our digital services. So that was my base number. In December 2017, we did. The difference between 2016’s non-campaign to 2017’s campaign: 45% increase in new users and 25.9% increase in borrows.

I then evaluated this to two previous emails and came up with two lessons: have a holiday email focusing on digital items and use “present” or “gift” in the subject line.

Last Email Unsubscribed From

I was curious as to which email list was driving unsubscribes. We switched to MailChimp in December 2013, so I evaluated data from that point to December 2017. It took awhile, but I was able to sort out the emails into different lists. When I asked MC if I could easily sort by the group the email was sent to, this was not an option. So by hand it went!

New Year’s Eve Playlist

I loved the idea of finding a song to dramatically finish the year with. My colleagues kindly supplied me with several songs. The project took longer to put together than I anticipated, but our weekly events email readers + Twitter had a good time with it.

Seasonal Bookmarks

This year, we set aside money to get 30 bookmarks printed and cut by a professional print shop. We’ve split this into three terms throughout the year. The first round was exhausting to push through with an August 1st due-in-house deadline. With the second round, it went more quickly. However, it wasn’t clear if the digital proofs were accurate or not. Thankfully, the printer sent a paper proof which I was able to sign off on last Friday. Today the two boxes arrived. My colleague was wonderfully helpful in helping me sort the bookmarks and then distribute them throughout the building. Today was the day that we wanted the bookmarks to be in-house. Good timing!

Logo for Midterms Event

My very talented colleague approached me about her redesign of our annual midterms programming. Usually, we stay open an extra hour for 3 days (and again for finals). This year, we’re going to have an after hours event on a Sunday. I was so inspired by the title, The Long Night Against Procrastination, that I wanted to make a custom logo.

Courtesy of Darien Library

I looked up Ikea lanterns to find my favorite version. Then I stabbed at Illustrator until I got the shape right. My colleague approved. The original text was just plain text at an angle. It didn’t fit my vision. So I spent about two days working at it to come up with the above design. It’s not perfect — I’m not confident with Illustrator yet — but it’s 85% of the way towards my vision.

Courtesy of Darien Library

I also rather like the email design that goes along with the logo.

I Aten’t Dead

~ Granny Weatherwax

I’ve been working on so many projects at work that it’s hard to remember that I need to keep this site updated on them. I’ve also been trying to stay on task with NaNoWriMo, so I’ve been preoccupied.

My latest page design for the Fellowship goes live on Friday. I’m rather pleased with it. I had quickly wireframed it and took it to the department head for approval. The finished version is almost identical to the sketch.

It also presented another challenge: film a new video with the current fellow and combine that clip with interviews from the two previous fellows. Their clips come from a video with a heavy music overlay on top of their speaking. I contacted the videographer/editor of that film. He sent me a similar piece of music. I ended up importing that video plus my filmed clip into Final Cut Pro. I sliced each section into pieces. Then I disabled clips I didn’t want to use until I came up with my winning combination.

The audio part was difficult. I ended up moving the new audio about 20 seconds under the previous clip and had them intertwined with a fade-in. When my new clip appears, the music shifts. If you’re listening closely, you’ll notice that the music from the first two shots has dropped out completely. However, for our purposes, it goes smoothly enough to get the job accomplished.

As well, for what feels like the first time in forever, I’m coming up with new ideas. One cherished suggestion was taken seriously by my boss and she brought it forth as a co-sponsored program. She then took it one step further and contacted the local public access channel to see if they’d want to film it. They do!

Design Development Work

No images of today’s work since I tossed out most of it when I was finished with the design process. My tasks for the day were to create a print ad and a double-sided bookmark. For the ad, I created 11 layouts. The first two were digital versions of the sketches that were handed to me. From there, I worked out different arrangements of text, font sizes, colors, adding a border, adding images, until I came upon the final design which was accepted. As you scroll through the previous designs, you can see how I slowly tipped my chessboard towards it. Each idea sliding downwards until I arrived at the final image.

I printed out the proofs, marked my two favorite, scribbled a note of guidance, and left them on the requester’s desk. Later, when I returned to pick up the pieces, he had also chosen my favorite one. While checking and re-checking the layout back in InDesign, I got frustrated with the tools. I nabbed the metal ruler from my colleague’s desk and held it up to my iMac’s surface. Then I slowed nudged the text box into place.

For the bookmark, I had looked at the theme idea last week in some confusion. I poked around on the web and found a bunch of photos for a moodboard of sorts and uploaded it the shared drive. On Friday, my colleague wrote back that I was close, but not quite right. I took her words, spent 2 minutes googling and found a lovely stock illustration. She loved it.

Today I started the work of creating a bookmark from that image. I’ll admit, these bookmarks aren’t quite the thing of dreams. I’m a little crunched for time, so it’s more important to get them done than perfect. For this project thought, I made a dozen or so layouts with different words and color choices. Sent it over. After lunch I had my response back. I then laid out 4 or so designs for the back and sent it back. Hopefully tomorrow I’ll get the sign-off so I can mark it as done.

We’re pushing a deadline of Friday here for these ten bookmarks. I’ve gotten works-in-progress drafts for six of them now. Once staff sign off on them, I can move them to the finish pile. The goal is to have all of them back from the print shop by September 1st for National Library Card Sign Up Month.

Planning Overall Library Program Themes for the Year

One of my jobs as publicity manager is to help departments find opportunities to work together on programs and services. As such, I found out their general program themes for the year. I then grouped these together in our wiki under two organizational schemes: by department and by season.

The information is pretty general so it remains flexible. For instance: Summer Reading Kick-Off: June. It doesn’t list the program theme or the actual start date. I arranged the seasonal information by ABC order. By department, by time.

I then immediately made use of the new resource by linking to it in an email for publicity planning, “Need help thinking ahead as to what we’ll be up to in this time period? View the seasonal program guide.”