달러(외화) 투자 관리 시스템 "외화드림 Lite"

이 글의 목차

1. 개발 배경
2. 주요 기능/특징
3. 설치 방법
4. 사용법/매뉴얼
5. 프로 버전에만 제공되는 기능
6. 주요 화면 예시

1. 개발 배경

해외 주식 거래를 위해, 또는 해외 여행을 준비하려고 외화를 보유하신 분들이 있을 것입니다. 최근에 토스뱅크를 비롯해 환전 수수료를 거의 무료로 제공해주는 곳이 많아지고 있어서, 외화 투자하기가 훨씬 수월해졌습니다.

외화 투자를 하다보면, 매수했을 때의 환율과 매도했을 때의 환율을 기록해서 손해보지 않고 팔았는지 잘 관리해야 합니다. 예를 들어 환율이 1300원일 때 USD2.00를 구매했다가, 환율이 1350일 때 USD1.00을 팔면 50원 이득이 생깁니다. 그러다 환율이 1400원일 때, 나머지 USD1.00을 팔면 100원의 이득이 생깁니다. 

박성현님의 달러 투자 관련 노하우(세븐 스플릿 투자)를 알게 되고, 저도 외화 구입(매수)과 매도를 몇 번 하다 보니, 위와 같이 한 번에 매수한 것을, 여러 번 나누어 매도할 때, 이것을 마땅히 관리하기가 쉽지 않았습니다. 박성현님이 만든 달러리치라는 앱이 있긴 한데, 모바일용 앱이다 보니, 데스크톱과 같은 넓은 화면에서 여러 가지를 한 눈에 보고 작업하기가 좀 까다로웠습니다. 

그래서 처음에는 구글 시트로 내역을 관리했었습니다. 그런데, 매수한 것을 여러 번에 나누어 매도할 때, 매수와 매도를 이어주는 것이 좀 복잡했습니다. 그래서 에어테이블(Airtable)이라는 맥가이버 칼 같은 데이터베이스 도구를 이용해 시스템을 만들어봤습니다.

외화드림 Lite의 실행 화면

2. 주요 기능/특징

외화드림 Lite 는 무료로 제공되며, 다음과 같은 주요 특징이 있습니다. 

• USD, EUR, JPY 등 총 17개의 외화를 사거나 판 내역을 관리합니다.
• 데스크톱과 모바일에서 사용 가능합니다.
• 매수한 항목에는 고유 번호가 붙어, 나중에 매도할 때 해당 매수 번호를 전액 또는 일부 매도할 수 있습니다. (분할 매도)
• 매수만 하고 아직 매도하지 않은 매수 항목은 현재 환율에 비추어 얼마나 수익이 날지 예측해 금액, 수익률, 색깔로 보여줍니다.
• 매도 완료한 항목도 해당 항목의 수익, 수익률, 색깔을 보여줍니다.
• 두 가지 UI가 제공됩니다: 
• 데이터 뷰 UI (에어테이블에 익숙한 경우)
• 인터페이스 UI (에어테이블에 익숙하지 않은 경우)
• 현재 투자 현황, 지금까지 투자 내역을 차트로 요약해서 시각적으로 보여줍니다.
• 현재 환율은 사용자가 직접 수동으로 업데이트합니다.
• 에어테이블에 무료 또는 팀 회원 가입이 되어 있어야 합니다.
• 개별 사용자 요구에 맞추어 본인이 무한 커스터마이징이 가능합니다.

다음은 외화드림 Lite가 할 수 없는 것입니다.

• 이 프로그램은 직접 매매하는 프로그램이 아니고, 매매 기록을 관리만 합니다. 매매는 은행, 증권사를 통해서 직접 하셔야 합니다.
• 최신 환율은 사용자가 직접 업데이트해야 합니다.
• 매수, 매도 타임을 알려주지 않습니다. 사용자가 직접 판단하셔야 합니다.
• 자동 매매를 지원하지 않습니다.

3. 설치 방법

외화드림 Lite 설치 방법

• 에어테이블에 무료 또는 팀 회원으로 가입니다. (이 링크로 가입하시면 저에게 크레딧이 조금 쌓입니다^^.)
• 외화드림 Lite 에 접속합니다.
• 화면 위쪽 외화드림 Lite 라는 베이스 이름 옆에 ⧉Copy base 링크를 눌러서 내 에어테이블의 워크스페이스로 베이스를 복사합니다. 
• 복사한 베이스에서 작업할 수 있습니다.
• 들어있는 예시 데이터는 참고하시고, 실제 사용 전에 모두 삭제하고 사용합니다. 
• 데스크톱에서는 외화드림 Lite (데이터 화면), 외화드림i (인터페이스 화면)의 두 가지 인터페이스가 제공됩니다.
• 모바일에서는 앱을 먼저 설치하셔야 합니다. 안드로이드용 Airtable, iOS용 Airtable
• 모바일에서 홈화면에 외화드림 Lite를 추가해놓고 쓰시면 더 편합니다.

4. 사용법/매뉴얼

간단한 외화드림 매뉴얼을 노션으로 만들어놨습니다.

5. 프로 버전에만 제공되는 기능

• 스크립트와 절대 URL이 있는 폼(form)을 개별 사용자에 맞게 수정 및 설치하는 작업을 도와드립니다. (원격 접속 또는 대면 접촉을 통한 지원)
• 외부 환율 사이트와 연동해 현재 환율을 정기적으로 자동으로 업데이트해줍니다. 
• 업데이트되는 환율이 정확하지 않은 경우, 수동으로 직접 업데이트하거나, [업데이트] 버튼을 눌러 업데이트할 수 있습니다.
• 매수 항목을 확인하고, 바로 그 자리에서 [매도] 버튼을 눌러 매도 진행이 가능합니다.
• 매수, 매도 등 진행할 때, 현재 환율, 오늘 날짜, 연관된 매수 항목 등이 기본값으로 주어져서 편리하게 입력할 수 있습니다.
• 캘린더와 타임라인으로 투자 내역(일지)를 확인할 수 있습니다.
• 수익이 나는 항목과 손해가 나는 항목을 레코드 색상으로 구분해줍니다.
• 환율 변환 계산기가 추가됩니다.
• 프로 버전은 유료(가격 미정)이며, 제 개인적으로 테스트중입니다. 
• 미래에 출시될 버전 2에서는 사용자가 에어테이블에 가입하지 않고 앱을 사용할 수 있도록 개발중입니다. 

6. 주요 화면 예시

화면 속의 데이터와 Lite 버전에 들어있는 데이터는 실제 데이터가 아닌, 예시 데이터입니다.

데이터 화면 - 매도 내역 (외화 종류별)

데이터 화면 - 매수 후 매도 완료한 내역(외화 종류별)

데이터 화면 - 매수 내역(아직 매도하지 않은 것)

데이터 화면 - 투자한 외화 종류별 기대 차익 조회. 현재 환율 조회 및 수정도 가능

인터페이스 화면 - 누적 수익 현황 자료. 월별, 일별 수익 차트

인터페이스 화면 - 매수 내역 전체 (매도 완료한 것 + 매도 안 한 것)

인터페이스 화면 - 매수 현황 (아직 매도 안 한 것)

인터페이스 화면 - 현재 투자 현황 차트, 예상 손익 차트

인터페이스 화면 - 환율 갤러리

인터페이스 화면 - 환율 업데이트

수 백 페이지 상담일지를 깔끔하게: Airtable Page designer

지난 번에 구글 설문(Google Forms)의 확장 프로그램인 폼 퍼블리셔(Form Publisher)를 이용해서 설문받은 결과지를 한 장 한 장 PDF 양식에 맞게 변환, 저장하는 방법을 알아보았습니다. 마찬가지로 잣폼(Jotform)이라는 툴을 이용해서 설문받은 결과지를 PDF 양식에 맞게 저장하는 방법도 살펴보았습니다.

이번에는 대표적인 노코드 툴인 에어테이블(Airtable)을 이용해서, 사용자에게 설문으로 받았거나, 또는 사용자가 직접 데이터에 입력한 내용들을 PDF 양식에 맞게 출력하는 방법을 알아봅니다. 실제 업무 활용 사례를 하나 공유합니다.

상담 관리와 상담 일지 정리

프로젝트 개요/문제

한 프로젝트에서는, 여러 명의 상담사가, 여러 내담자를 여러 번 상담하는데, 매회 상담시마다 상담일지를 적어야 했었습니다. 이런 상담 운영은 관리 시스템이 있지 않으면, 여러 상담사의 일정을 조율하고, 상담 시간을 배치고, 원하는 문서를 제시간에 수집하고, 필요할 때에 안내를 보내는 것이 상당히 어렵습니다. 프로젝트 기간이 달랑 5~6개월 이내인데, 그것을 위해서 거대한 관리 시스템을 개발하려면 배보다 배꼽이 커지게 됩니다. 그렇다고, 적당히 엑셀이나 구글 시트로 운영을 하기에는 너무 복잡하기도 하고, 일정 관리, 협업이 어렵습니다.

상담 일지도 문제였습니다. 상담 일지에는 반복적으로, 상담자, 내담자, 상담장소, 시간, 유형 등이 들어가고, 상세한 상담 내용을 적어야 합니다. 이것을 처음에는 파워포인트나 워드프로세서 양식으로 만들어서 양식을 채워오라고 상담사에게 부탁했습니다. 그러나 사람마다 양식을 조금씩 변경해서 쓰기도 하고, 글꼴, 문단, 페이지 나누는 방법 등이 개인마다 미묘하게 달라지기도 합니다. 가장 큰 문제는 결과가 파일로 나오기 때문에 파일명을 일관성있게 붙이고, 상담사는 파일을 제출하고, 관리자는 취합해서 파일 관리를 하나하나 꼼꼼하게 해주어야 합니다.

솔루션: Airtable 기반의 상담 관리 시스템

그래서, 에어테이블로 상담 관리 시스템을 만들었습니다. 실제 초기 구축은 3~4시간 정도에 끝났고, 운영하면서 조금씩 보완해나갔습니다. 상담이 남아있는 내담자는 누구이고, 완료된 내담자는 누구이며, 상담사별로 얼마나 상담을 했고, 어떤 일정이 근간에 예정되어 있고, 지금 어떻게 진행되고 있는지 등이 한 눈에 보이는 것은 당연합니다.

Airtable로 3~4시간만에 구현된 상담관리 시스템. 상담일정 테이블 뷰

상담관리 시스템. 캘린더 뷰

상담 일지 입력

제가 추가로 기대했던 것은 바로 상담 일지의 관리였습니다. 각 상담사들이 에어테이블에 직접 들어가서 상담일지 작성 필드에 상담 내용을 서식이 있는 리치 텍스트(rich text)로 입력할 수 있게 했습니다. 에어테이블의 리치 텍스트 입력은 마크다운(Markdown)을 지원하며, 마크다운에 익숙하지 않은 사람들을 위해 아래 그림과 같이 보조 툴박스가 떠서, 금방 익숙하게 서식이 있는 문서 작성이 가능합니다.

Airtable에서 상담사가 상담일지를 입력하는 화면. Rich text 입력을 위한 툴박스가 제공되며, Markdown도 쓸 수 있다.

결과보고 및 상담 일지 출력

6개월 정도 되는 프로젝트가 종료되고, 발주처에 상담일지 원본을 상담사별, 내담자별, 주제별, 날짜별 등으로 정렬하여 제출해야 했습니다. 만약에 상담 일지를 파일로 만들었다면, 파일들 정리하느라고 하세월이 걸렸을 것입니다. 에어테이블에는 상담 일정 등 메타 데이터와 상담 내용(상담 일지)가 같이 정리되어 있으니, 원하는 방식으로 그룹핑하고, 정렬하는 것은 매우 쉬운 일입니다. 그리고, 상담 일지 양식은 에어테이블의 페이지 디자이너(Page designer)를 이용해서 1시간 정도 걸려서 템플릿을 만들었습니다.

Airtable의 Page Designer를 이용해 상담일지 템플릿을 만드는 모습

그리고, 드디어! 데이터베이스에 상담사들이 입력한 수백 건의 데이터와 내용들이 다음과 같이 깔끔하게 상담일지 양식으로 변환이 되었습니다. 그리고 수 백 페이지의 상담 일지는 그냥 클릭 한 번으로 PDF 파일 하나로 제작되었습니다.

상담일지를 PDF로 출력한 예시. 상담 한 건이 한 장으로 생성되며, 수 백 장도 한꺼번에 생성된다.

데이터베이스의 리포트 생성

데이터가 많고, 계속 변한다면, 그것의 구조를 만들고, 관리하는 것(structure, logic)과, 보여주는 것(presentation)이 분리되는 것이 바람직하겠죠.

구글 설문에 들어간 데이터, 그리고, 잣폼에 들어간 데이터도 마찬가지였습니다. 데이터 입력은 편하게 하되, 보여주는 아웃풋은 예쁘게 원하는 모양으로 보여야 합니다. 그래서 반복적인 템플릿이나 반복적인 데이터가 있는 자료들은 되도록이면 데이터베이스를 이용해 관리하는 것이 좋겠지요. 일상적인 업무에서도 데이터를 기반으로, 원하는 형식으로 만드는 일이 많이 있습니다. 예를 들면, 수 백 명의 상장, 수료증을 만든다거나, 인보이스를 출력한다거나, 재직증명서를 만든다거나, 특정한 형식의 요청서를 만드는 경우, 여러 명의 주소를 기반으로 우편 레이블을 만드는 경우, 수백 명의 고객에게 이름과 몇 가지 정보만 바꿔서 메일을 보내는 경우 등입니다. 이런 문서들은 양이 적으면, 워드프로세서 프로그램의 템플릿을 이용해 직접 작성할 수도 있지만, 조금 양이 많아지면, 보통 스프레드시트와 연계하여 소위 "메일 머지"를 사용해서 만들 수 있습니다. 그러나 데이터가 지속적으로 들어오고, 계속 업데이트되며, 여러 사람으로부터 수집해야 하는 데이터라면 데이터를 입력, 수집, 관리하는 시스템이 같이 따라줘야 합니다. 그리고 최종적으로 데이터를 원하는 형식(포맷)으로 만들어서 출력해주는 리포트(Report) 기능이 필요합니다.

간단한 데이터베이스와 쉬운 리포팅을 구현하는 방법으로 지금까지 구글 설문의 폼 퍼블리셔, 잣폼의 PDF 생성 기능, 그리고 에어테이블의 페이지 디자이너를 살펴보았습니다. 각각의 경우 약간의 특징과 장단점이 있습니다.

스프레드시트(엑셀, 구글시트 등)와 워드프로세서 메일 머지(워드, 한글 등)

• 변화가 없는 한정된 데이터를 가지고 있고, 특별히 입력 인터페이스는 필요없으며, 딱 한 번 작업하면 끝나는 경우에 적합.
• 워드프로세서에서 스프레드시트 데이터를 한 번 불러오면 끝남.
• 보통 우편 레이블 출력, 상장 출력, 청구서 등에 많이 활용

구글 설문의 폼 퍼블리셔(Form Publisher for Google Forms)

• 사용자로부터 설문으로 받은 내용을 원하는 양식으로 변환하여 출력할 때 유용
• 최종 출력 양식에 PDF 뿐 아니라, 워드프로세서, 스프레드시트, 프리젠테이션 프로그램을 지정할 수 있어서 유연함.
• 양식 하나하나는 파일로 관리되어 편하기도 하지만, 나중에 대량으로 정리할 때에는 번거로울 수도 있음.
• 계산, 차트 등의 기능을 이용해서 매우 복잡한 개인별 리포트를 생성 가능(예: 심리검사 결과 리포트)

잣폼의 PDF 변환

• 페이퍼로 된 PDF 파일을 온라인 설문으로 변환시킬 때 편리함.
• 특히, 서명이 포함되어 있는 경우도 온라인으로 바로 처리할 수 있어서 편리함.
• 사용자로부터 받은 데이터는 깔끔하게 잣폼 테이블 데이터로 관리되고, 각종 연산 처리가 가능함.
• 개별 또는 여러 개의 PDF 파일을 만들 수 있음. 기능이 많다 보니 사용법이 약간 까다로운 편

에어테이블의 페이지 디자이너 (Page designer for Airtable)

• 초기에 데이터베이스를 구축해야 한다는 점에서 부담이 있음.
• 강력한 데이터베이스를 기반으로 한, 관리자용, 사용자용 인터페이스 구축이 비교적 쉽게 이루어짐.
• PDF 리포트도 파일이 아니라, 데이터베이스에 들어가 있으므로, 개별 또는 그룹별로 관리하기가 쉬움.

Read more "수 백 페이지 상담일지를 깔끔하게: Airtable Page designer"

Airtable 자동화 예시: 내일 이벤트를 영업일 하루 전에 자동으로 안내

요즘 많은 노코드 툴들이 그러하듯이, 에어테이블(Airtable)에도 강력한 자동화(automations) 기능이 있습니다. 즉, 여러 앱들간의 프로세스를 엮어주는 재피어(Zapier)나 메이크(Make) 등을 쓰지 않아도, 상당한 수준의 워크플로우 자동화를 구축할 수 있습니다. 저의 개인적인 경험으로는 재피어나 메이크는 트리거(trigger)나 액션(action)을 정의할 때, 앱 내부의 동작을 세부적으로 정의하는 데에는 좀 한계가 있었습니다.

오늘은 아주 초보적인 자동화를 예시로 소개합니다. 예전에 다루었던 행사 운영 관리 베이스에 간단한 자동화를 얹어보겠습니다. 행사 운영 관리 베이스에는 매일 이벤트(교육, 행사, 모임 등)가 있고, 이벤트에는 담당 강사가 있습니다. 강사는 별도의 테이블에 연결된 Linked records 필드이고, 강사 이메일은 Lookup 필드입니다.

강사들은 조직 내부 구성원이 아니므로, 이벤트를 까먹지 않고 준비하도록 별도의 안내를 보내려고 합니다. 10일 전 안내, 3일 전 안내, 하루 전 안내 등 필요한 시기에 안내를 메일이나 문자로 보내주면 좋겠지요. 이번 예시에서는 하루 전에 자동 안내 메일을 보내봅니다.

자동화할 작업 정의 및 시작하기

무엇을 자동화할 것인지 명확히 하는 게 좋겠죠. 날마다 있는 행사에서 일일이 안내 메일을 보내기 어려우니, 정확히 하루 전에 행사 담당 강사에게 자동으로 행사 안내 메일을 보내기로 합니다. 에어테이블에서 자동화 시작은, 화면 왼쪽 위에 베이스 이름 옆에 [Automations]를 누르면 시작합니다. 참고로, [Data]는 기본적으로 테이블과 컬럼을 만들고, 데이터를 추가하는 곳이고, [Interfaces]는 데이터 시각화 툴입니다.

에어테이블 화면의 왼쪽 위에, 베이스 이름 옆에서, Automations를 선택해서 자동화 화면으로 들어갑니다.

트리거(trigger) 설정

가장 간단한 자동화는 트리거 하나와 액션 하나로 구성됩니다.

트리거는 자동화를 일으키는 조건입니다. "만약 행사가 다음 날 있으면, 안내 메일을 보내라!"라는 자동화에서, "만약 행사가 다음 날 있으면"이라는 것이 바로 트리거입니다.

에어테이블 트리거 유형(trigger type)은 주로 다음과 같은 것들이 있습니다.(물론 그 밖에 외부 앱과 직접 연동되는 트리거도 있습니다.)

When record matches conditions

레코드의 데이터 값이 특정 조건을 만족할 때

When a form is submitted

사용자자 양식(form)에 값을 제출할 때

When record created

새로운 레코드가 생성될 때

When record updated

기존 레코드 값이 변경될 때

When record enters view

특정 뷰를 기준으로 레코드가 생성될 때(정확히는, 조건에 맞는 레코드가 들어올 때)

At scheduled time

특정 시간이 되면

When webhook recieved

(외부 앱에서) 웹훅 이벤트를 받을 때

When a button is clicked

버튼(필드)이 눌릴 때

에어테이블에서 자동화 시발점인 트리거의 종류

우리는 제일 첫 번째 트리거를 사용합니다. 행사 운영 관리 베이스에서는 영업일 기준으로 {시작일} 필드의 날짜를 검사하는 수식을 이미 만들어 놓았습니다. 따라서 조금 더 구체적으로 트리거를 다음과 같이 정의할 수 있습니다. {영업일 기준}의 값이 "다음날"이면, 뭔가 액션을 취해라. 간단하죠? 아래 그림과 같이 트리거 유형, 테이블, 조건(conditions)를 차례로 설정해주면 됩니다.

트리거 유형에서 레코드가 특정 조건에 맞을 때를 선택하고, 테이블은 "일정" 테이블을, 조건은 {영업일 기준} 필드값이 "다음날"인 경우로 설정했다.

액션(actions) 설정

트리거 조건을 만족하면, 무엇을 시킬 것인가가 바로 액션이죠. 우리는 강사에게 메일을 보냅니다. 다음과 같은 액션 유형(action type) 중에 Send email을 고르면 됩니다.

Send email

메일을 보낸다.

Create record

새로운 레코드를 생성(추가)한다.

Update record

기존 레코드 값을 변경한다.

Find records

(조건에 맞는) 레코드를 찾아낸다.

Run script

스크립트를 실행한다.

에어테이블 자동화에서 액션의 유형

이 밖에도, 에어테이블에서 직접 슬랙이나 마이크로소프트 팀즈에 메시지를 보낸다든지, 구글 캘린더, 구글 시트, 지라 등에 내용을 추가하는 등의 액션 유형도 있습니다.

메일을 보내기로 했으니까, 수신자, 주제, 메일 내용과 같은 필수값들을 설정해주면 됩니다. 물론 데이터베이스에서 특정 필드를 변수로 포함시킬 수 있습니다. 특정 필드값을 변수로 사용하기 위해서는 파란색 ⊞ 버튼을 누르고, Insert value from field 목록에서 원하는 필드명을 고르면 됩니다.

액션에 대한 레이블(label)은 "강사 안내 메일"로 설명을 넣어줍니다.

수신자에는 {강사 메일}이라는 이메일 주소값 필드를 넣어줍니다.

메일 제목은 아래와 같이 작성했습니다.

다음 날({시작일}) {이벤트} 안내

액션 설정: 액션의 제목을 넣어주고, 이어서, 메일 수신자, 메일 제목을 넣습니다.

메일 내용에는 간단히 아래와 같이 세 개의 필드를 포함시켰습니다.

{강사}님, 안녕하세요?
{시작일}에 {이벤트}이/가 시작되어요. 
미리 준비하시고, 당일날 늦지 않게 와주세요. 
감사합니다.

메일 내용 설정 화면

테스트 및 미리 보기

이제 트리거와 액션을 만들었으니, [Generate a preview] 버튼을 눌러 메일 모양이 어떻게 나올지 확인해봅니다. 또는 [View results] 버튼을 눌러 자동화 실행 결과가 성공적인지 확인해봅니다.

액션 결과 미리 보기

활성화(activate)

마지막으로 방금 만든 자동화가 아직 비활성화(inactive) 상태이므로, 활성화(active) 상태로 바꿔줍니다.

지금까지 정의한 자동화를 활성 상태(active)로 바꿔준다.

이제 다 되었습니다. 앞으로는 운영자들이 일일이 사전 안내를 하지 않아도, 담당 강사에게 하루 전 메일이 자동으로 나갑니다. 물론 이 예시에서는 극단적으로 간단한 조건들을 사용했지만, 보다 복잡한 조건, 복합 트리거, 복합 액션, 조건부 분기 액션을 만드는 것도 크게 어렵지 않습니다.

다음 기회에는 메일 말고, 문자나 카카오톡 메시지를 자동으로 보내는 것을 살펴보겠습니다.

Read more "Airtable 자동화 예시: 내일 이벤트를 영업일 하루 전에 자동으로 안내"

SQL과 연관지어본 Airtable: linked records, lookup, count, rollup 필드

에어테이블(Airtable)은 기본적으로 관계형 데이터베이스입니다. 요즘에는 비정형 데이터와 빅데이터가 부상하면서 NoSQL 데이터베이스도 많이 쓰이지만, 여전히 관계형 데이터베이스는 매우 중요합니다. 에어테이블을 사용하기 위해 데이터베이스의 개념이나 질의 언어인 SQL을 알 필요는 전혀 없습니다. 그러나, 기존에 SQL을 조금이라도 써왔던 분들은, SQL과 연관시켜서 에어테이블의 주요 개념들을 좀 더 쉽게 이해할 수 있을 것입니다.

에어테이블에서 한 개의 데이터베이스를 베이스(base)라고 부릅니다. 그리고 베이스 안에는 여러 개의 테이블(table)을 담을 수 있습니다. 다시 테이블은 여러 개의 다른 방식으로 표현된 뷰(view)를 가질 수 있습니다. 이 때 기본적으로 테이블과 테이블이 관계를 맺으면(에어테이블에서 link to라고 표현함. SQL에서 말하는 join), 현재의 테이블에 관계 맺어진 다른 테이블의 값을 불러오거나(lookup), 가져와서 요약 정리하거나(rollup), 갯수를 셀 수(count) 있습니다. 이 글에서는, 관계형 데이터베이스를 설명하려는 것은 아니므로, 간단히 에어테이블에서 이런 것들이 어떤 필드로 표현되는지, 그것을 SQL로 표현하면 어떻게 대응되는지만 살펴봅니다.

어떤 예제로 설명을 해야 하나 고민하다가, 그냥 제가 개인적으로 사용하고 있는 독서 목록이라는 베이스를 사용하기로 했습니다. 읽은 책들을 정리하기 위한 베이스의 구조는 아주 간단합니다. 책 목록과 작가 목록 두 개의 테이블로 되어 있고, 책의 저자가 작가의 이름과 연결(link)되어 있습니다. 책 목록에 같은 작가가 쓴 책이 여러 권 있을 때, 작가 이름을 매번 새롭게 치는 것이 아니고, 작가 테이블에서 불러온다는 것입니다.

예시 (데이터)베이스

예전부터 공개된 독서 카드(특정 뷰)

에어테이블에서는 미리 테이블 안에 linked records 필드 (또는 link to 필드), lookup 필드, count 필드, rollup 필드를 만들어놓게 되므로, 컬럼(필드) 수가 다소 많아지게 됩니다. 그래서, 필요 없는 컬럼은 숨기기하면서 목적에 맞는 여러 개의 뷰(view)를 만들면 됩니다.

실제로는 아래 두 개의 테이블명과 필드명으로 한글을 사용했습니다. 다만, 일반적인 데이터베이스에서 한글을 테이블과 필드 이름으로 사용하는 데에는 제약이 따르므로, 여기 예시에서는 편의상 영문으로 합니다.

Books 테이블 구조

필드명	필드 유형	비고
Book_title	Single line text	Primary field
Author	Linked records	저자 테이블과 연결
Rating	Rating	1점~5점
About_author	Lookup	저자 테이블에서 인물소개 값을 참조

Authors 테이블 구조

필드명	필드 유형	비고
Author_name	Single line text	Primary field
Description	Long text	저자에 대한 소개글
Books	Linked records	해당 저자가 쓴 책 목록. 책 테이블과 연결
N_of_books	Count	이 작가의 책이 책 테이블에 몇 개 있는지 셈
Average_rating	Rollup	이 작가가 쓴 책의 평균 별점 계산

Linked records 필드

에어테이블 확장 프로그램인 Base schema를 이용해 독서 목록이라는 베이스의 스키마를 시각적으로 표현한 그림. 두 테이블 사이에 Linked records 필드만 보여주도록 설정한 경우

Linked records 필드는 두 개의 테이블을 연결하는 것입니다. Books 테이블에서 저자는, Authors 테이블에 있는 저자명과 연결됩니다. 에어테이블에서는 한 테이블에서 Link to 필드를 만들면, 자동으로 다른 테이블에도 Link to 필드가 생깁니다. 다시 말해, Books 테이블의 저자를 Authors 테이블의 저자명과 연결하면, 거꾸로 Authors 테이블에는 Books 테이블의 책이름과 연결되는 Link to 필드가 자동 생성되어, 해당 저자가 쓴 책 목록이 모두 표시됩니다. Books 테이블에서는 글쓴이가 보여지는 방식을 SQL로 표현하면 다음과 같이 되겠지요.

SELECT b.book_title, a.author
FROM books b
INNER JOIN authors a ON b.author = a.author_name;

Lookup 필드

Lookup 필드는 연결된 다른 테이블에서 특정 필드 값을 조회해서 가져오는 것입니다. Books 테이블에서는 매번 저자의 인물 소개가 들어가지 않지만, Authors 테이블에서 Description의 값을 조회해서 보여주는 방식입니다.

SELECT b.book_title, b.author, a.description AS about_author
FROM books b
INNER JOIN authors a ON b.author = a.author_name;

Count 필드

에어테이블에서 Count라는 유형의 필드는 실제 SQL에서 count 함수를 쓰는 것과 비슷합니다. 아래 쿼리는 저자별로 Books 테이블에 몇 권의 책이 있는지 세어서 보여줍니다.

SELECT a.author_name, COUNT(*) AS n_of_books
FROM books b INNER JOIN authors a 
ON b.author = a.author_name
GROUP BY a.author_name;

Rollup 필드

에어테이블의 Rollup 필드는 연결된 다른 테이블의 특정 필드를 요약해서 보여줄 때 사용합니다. 위에서 저자별로 여러 권의 책이 있고, 각 책들의 별점이 있는데, 저자별로 모든 책들의 별점의 평균을 보여주기 위해 Authors 테이블에 Rollup 필드를 넣었습니다. Rollup 필드는 평균 외에도, 합계, 갯수, 최대값, 최소값, 문자열 함수, 논리 함수 등 여러 가지를 사용해서 요약할 수 있습니다.

에어테이블의 평균별점 필드의 수식은 다음과 같이 정의됩니다. 별점이 있는 경우, 있는 레코드만 모아서 평균을 보여달라는 것이죠.

IF(values, AVERAGE(values))

SQL에서 저자와 저자별 별점 평균을 보여주려면 다음과 같이 하면 되겠죠. (소수 첫째 자리까지만)

SELECT a.author_name, ROUND(AVG(b.rating), 1) AS average_rating
FROM books b
INNER JOIN authors a ON b.author = a.author_name
GROUP BY a.author_name;

제가 겪은 실무에서는, 단순하게 lookup, rollup, count를 쓰기보다는 조건부(conditional) 필드를 사실 더 많이 쓰게 됩니다. 에어테이블에서는 상당히 직관적으로 코딩 없이 조건부 lookup, rollup, count를 사용할 수 있고, 그것에 따라 여러 개의 뷰를 만들 수 있습니다. 그리고 각각의 뷰에서 할 수 있는 일을 제한해놓습니다. 그러면, 데이터베이스나 프로그래밍에 대한 지식이 거의 없어도, 상당히 복잡한 업무 처리를 이것만으로 구현하게 됩니다.

Read more "SQL과 연관지어본 Airtable: linked records, lookup, count, rollup 필드"

Airtable 팁: 2024년 공휴일을 반영한 영업일 기준 전날, 다음날 계산식

에어테이블(Airtable)은 관계형 데이터베이스를 기반으로 매우 쉽고, 매우 강력한 노코드 앱 개발 환경을 제공합니다. 스프레드시트와 유사하지만, 훨씬 다재다능하며, 워크플로우(workflow), 자동화, 데이터 시각화나 비즈니스 인텔리전스(BI)를 쉽게 구축할 수 있으며, 데스크톱 또는 모바일 앱 또는 웹을 쉽게 만들 수도 있습니다. 앞으로 Airtable과 관련한 다양한 이야기와 팁을 하나씩 정리해보겠습니다.

두 날짜의 차이 판단하여 이름 붙이기

Airtable에서 {시작일}이라는 필드가 있다고 했을 때, 그 시작일이 현재를 기준으로 다음날인지, 전날인지, 아니면 이틀 후인지 등은 DATETIME_DIFF 함수를 통해 쉽게 파악할 수 있습니다.

SWITCH(     
	DATETIME_DIFF({시작일}, NOW(), 'days'), 
	2, '모레',
	1, '내일',
	0, '오늘',
	-1, '어제',
	-2, '그제' )

이런 식입니다. 그런데, 이 수식에는 몇 가지 문제가 있습니다. 

첫째, 시간대가 반영되지 않았습니다. 우리 나라는 표준 시간(UTC, Universal Time Coordinated)보다 9시간 빠르므로, 위와 같이 하면, 9시간의 오차가 계속 나게 됩니다. 즉, 필드 값이 어제 오후 3시가 넘어가면, 위의 수식에서는 그냥 '오늘'이라는 값을 리턴해버립니다.

둘째, 영업일(working days, business days)이 반영되지 않았습니다. 실제 Airtable을 비즈니스 현장에서 쓸 때에는 영업일을 기준으로 날짜 계산이 필요한 경우가 많습니다. 예를 들어, 예정 일정 하루 전에 반드시 (자동 또는 수동으로) 이메일/문자 알림을 보내거나, 고객에게 전화를 걸어야 하는 업무가 있다고 가정해보겠습니다. 다음 주 월요일이 교육 시작일인데, 오늘은 전주 금요일입니다. 그러면 오늘은 그냥 날짜 상으로는 3일 전이지만, 영업일 기준으로는 하루 전입니다. 그래서 오늘 반드시 고객에게 전화를 걸어야 합니다. 

2024년 9월 달력. 영업일 기준 다음날과 캘린더 기준 내일이 완전히 다른 경우

    

영업일을 반영하려면, 주말인 토요일, 일요일을 일단 계산에서 제외해야 합니다. 그래도 문제가 있습니다. 주말은 아니지만, 공휴일, 소위 말하는 빨간 날을 제외해야 제대로 된 영업일 기준의 날짜 계산을 해줍니다. 그냥 수동으로 업무를 처리할 때에는, 그 정도는 사람이 감안해서 미리미리 처리해주면 되겠지만, 모든 것이 자동으로 처리되는 대량의 데이터 시스템에서는, 이런 계산을 대충 하게 되면 큰일 납니다.

시간대, 영업일을 고려한 날짜 차이 계산식

따라서 다음과 같이 보완을 해줍니다.

첫째, 시간대를 반영해서, Airtable 현재 시스템 시간에 DATEADD 함수를 이용해 9시간을 더합니다.

DATEADD(NOW(), 9, 'hours')

둘째, 구해진 시간을 한국 시간대로  설정해줍니다. SET_TIMEZONE 함수는 첫 번째 파라미터의 시간을 두 번째 파라미터에서 표시한 시간대로 표시해 시간값을 리턴해줍니다. 시간대별 표시자(identifier) 목록을 참고하십시오. (Airtable 필드 형식(Formatting) 설정에서 시간대를 바꿔주는 옵션이 있습니다만, 이것은 손대지 않는다고 가정합니다.) 추가로, 편의상 DATESTR 함수를 써서 일정 표시를 ISO 8601 날짜 표현방식에 따라, YYYY-MM-DD로 바꿔주겠습니다.

DATESTR(SET_TIMEZONE(DATEADD(NOW(), 9, 'hours'), 'Asia/Seoul'))

셋째, 이렇게 구한 현재 날짜/시간과 {시작일} 필드값을 비교해야 합니다. 그런데, 위에서 썼던 DATETIME_DIFF 함수를 쓰면, 주말이나 공휴일을 고려하지 않고, 날짜 차이를 계산합니다. 약간 주의할 점은, 시작일과 끝일을 모두 포함하여 계산하니, 양수인 경우 +1이, 음수인 경우 -1을 더한 값이 나옵니다.WORKDAY_DIFF라는 영업일 계산 함수를 써서, 일단 주말을 고려하여 날짜 차이를 계산합니다.

WORKDAY_DIFF(
  	DATESTR(SET_TIMEZONE(DATEADD(NOW(), 9, 'hours'), 'Asia/Seoul')),
    {시작일}
)

이렇게 하면, 시작일에서 현재일 사이에 영업일이 며칠 있는지 계산해줍니다. 그 값이 마이너스이면, 시작일이 이미 지났다는 뜻이고, 양수이면 시작일이 미래라는 뜻입니다. 결과가 0이면 영업일이 하나도 없으니, 시작일과 현재일이 모두 휴일이라는 뜻입니다. 1 또는 -1이면 시작일이나 현재일 둘 중의 하나가 영업일이라는 뜻입니다.

넷째, WORKDAY_DIFF 함수에서 세 번째 파라미터에 주말이 아닌 공휴일을 추가합니다. 2024년 기준 대한민국 공휴일을 참조하여, 마지막으로 공휴일 목록을 하나의 문자열로 묶어서 넣습니다. 

WORKDAY_DIFF(
	DATESTR(SET_TIMEZONE(DATEADD(NOW(), 9, 'hours'), 'Asia/Seoul')),
    {시작일}, 
    '2023-01-23, 2023-01-24, 2023-03-01, 2023-05-05, 2023-06-06, 2023-08-15, 2023-09-28, 2023-09-29, 2023-10-02, 2023-10-03, 2023-10-09, 2023-12-25, 2024-01-01, 2024-02-09, 2024-03-01, 2024-05-15, 2024-06-06, 2024-08-15, 2024-09-16, 2024-09-17, 2024-09-18, 2024-10-03, 2024-10-09, 2024-12-25'
)

다섯째, 다시 SWITCH 조건문에서 위의 값을 검사하여, 전전날, 전날, 오늘, 다음날, 담담날을 표시해주도록 합니다.

SWITCH(
	WORKDAY_DIFF(DATESTR(SET_TIMEZONE(DATEADD(NOW(), 9, 'hours'), 'Asia/Seoul')), {시작일}, '2023-01-23, 2023-01-24, 2023-03-01, 2023-05-05, 2023-06-06, 2023-08-15, 2023-09-28, 2023-09-29, 2023-10-02, 2023-10-03, 2023-10-09, 2023-12-25, 2024-01-01, 2024-02-09, 2024-03-01, 2024-05-15, 2024-06-06, 2024-08-15, 2024-09-16, 2024-09-17, 2024-09-18, 2024-10-03, 2024-10-09, 2024-12-25'),
	-3,'전전날',
	-2,'전날',
    -1,'(지난)오늘',
    0, '휴일',
	1,'(올)오늘',
	2,'다음날',
	3,'담담날'
)

실제 베이스 예시: 행사 운영 관리

아래에 예시로 만들어놓은 '행사운영관리'라는 베이스를 살펴보십시오. {시작일}이 오늘인지, 전날인지, 다음 날인지 판단하여 {영업일 기준}이라는 필드에서 결과를 보여줍니다. 시작일 하루 전에는 '사전 안내'를 반드시 해야 하고, 당일은 '현장 진행'을 해야 하고, 하루가 지나면 '해피콜'을 반드시 해야 한다고 합시다. 그런 업무를 체크리스트로 만들어놓고, 전날 일정, 오늘 일정, 다음날 일정 등만 따로 볼 수 있는 뷰(View)를 만들어놓으면, 누락이 없는 업무 관리 시스템이 됩니다. 또 거기에 전날, 오늘, 다음날 해야 하는 일들을 자동화(automation)로 걸어놓으면, 업무 담당자의 반복적인 업무가 획기적으로 줄어들겠죠? 이런 이야기는 다음 기회에...

Read more "Airtable 팁: 2024년 공휴일을 반영한 영업일 기준 전날, 다음날 계산식"