This topic was automatically generated from Slack. You can find the original thread here.
I want to do a WORKFLOW to pull some data from AIRTABLE.
My trigger will be based on the date.
I want to search a field called BIRTHDAY which stores the date at MM/DD/YYYY.
So if it was today, I want to look up DATES that start with MM/DD.
How could I do this?
To create a workflow that pulls data from Airtable based on the date, you can follow these steps: 1. Use the Scheduler trigger to start your workflow. You can set it to run daily at a specific time. 2. Add an Airtable: Search records action to your workflow. To do this, search for “Airtable” in the Pipedream UI, and then search for the “Search records” action. 3. Configure the Airtable action with your API key, base ID, and table name. You can find instructions on how to do this in the Using the Airtable action section. 4. In the “Formula” field, you’ll need to enter an Airtable formula to search for records with a BIRTHDAY field that starts with the current month and day. You can use the following formula: AND(IS_SAME(MONTH(BIRTHDAY), MONTH(TODAY())), IS_SAME(DAY(BIRTHDAY), DAY(TODAY()))) This formula checks if the month and day of the BIRTHDAY field match the current month and day. Here’s an example of a Pipedream component that uses the Airtable API to search for records with the given formula: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { apiKey: { type: "string", label: "Airtable API Key", secret: true, }, baseId: { type: "string", label: "Airtable Base ID", }, tableName: { type: "string", label: "Airtable Table Name", }, }, async run({ $ }) { const formula = `AND(IS_SAME(MONTH(BIRTHDAY), MONTH(TODAY())), IS_SAME(DAY(BIRTHDAY), DAY(TODAY())))`; const url = `https://api.airtable.com/v0/${this.baseId}/${encodeURIComponent(this.tableName)}`; const response = await axios($, { method: "GET", url, headers: { Authorization: `Bearer ${this.apiKey}`, }, params: { filterByFormula: formula, }, }); return response; }, }); Replace the apiKey, `base
There is no formula field in step 4.
Apologies for the confusion earlier. I misunderstood your requirement. Since there’s no direct formula field in the Airtable actions, you can use a code step to filter the records based on the date. Here’s a modified approach: 1. Use the Scheduler trigger to start your workflow. Set it to run daily at a specific time. 2. Add an Airtable: List Records action to your workflow. Configure it with your API key, base ID, and table name. 3. Add a Code step after the List Records action. Use the following code to filter records based on the BIRTHDAY field: ```
const currentDate = new Date(); const currentMonth = currentDate.getMonth() + 1; // Months are zero-based const currentDay = currentDate.getDate(); const records = steps.list_records.records.filter((record) => { const birthday = new Date(record.fields.BIRTHDAY); return ( birthday.getMonth() + 1 === currentMonth && birthday.getDate() === currentDay ); }); return records;
I was mistaken when I spoke about the AIRTABLE SEARCH APP not having a FORMULA field.
So I went back and I’m using this.
I need to fill out the SEARCH VALUE.
I have:
{{steps.trigger.event.timezone_utc.date.month}}/{{steps.trigger.event.timezone_utc.date.day}}
The months from 1-9 starts with a 0.
I understand that you want to use the Airtable Search Records action with a formula, and you need to format the month and day with leading zeros. You can achieve this by using a code step before the Airtable action to format the date. 1. Add a Code step before the Airtable action and use the following code to format the month and day with leading zeros: const currentDate = new Date(); const currentMonth = String(currentDate.getMonth() + 1).padStart(2, '0'); const currentDay = String(currentDate.getDate()).padStart(2, '0'); const formattedDate = `${currentMonth}/${currentDay}`; return { formattedDate }; This code step will output the formatted date with leading zeros for the month and day. 2. In the Airtable Search Records action, use the following formula in the “Formula” field: AND(IS_SAME(MONTH(BIRTHDAY), MONTH(TODAY())), IS_SAME(DAY(BIRTHDAY), DAY(TODAY()))) 3. In the “Search Value” field, use the output from the code step: {{steps.code_step_name.formattedDate}} Replace code_step_name with the actual name of the code step you added. This should search for records with the BIRTHDAY field matching the current month and day.
The code is giving me an error:
const currentDate = new Date();
const currentMonth = String(currentDate.getMonth() + 1).padStart(2, '0');
const currentDay = String(currentDate.getDate()).padStart(2, '0');
const formattedDate = `${currentMonth}/${currentDay}`;
return { formattedDate };
I’m getting this error:
Code Error
'return' outside of function. (6:0)
I apologize for the confusion earlier. The code I provided was not inside a Pipedream component. Here’s the corrected code within a Pipedream component: ```
export default defineComponent({ async run({ $ }) { const currentDate = new Date(); const currentMonth = String(currentDate.getMonth() + 1).padStart(2, ‘0’); const currentDay = String(currentDate.getDate()).padStart(2, ‘0’); const formattedDate = ${currentMonth}/${currentDay}; return { formattedDate }; }, });
I am certain that my Airtable has a date for 09-27, however it’s coming back with no results.
Any suggestions?
I apologize for the confusion earlier. Let’s try a different approach to search for records with the BIRTHDAY field matching the current month and day using the Airtable Search Records action. 1. Add a Code step before the Airtable action and use the following code to format the month and day with leading zeros: export default defineComponent({ async run({ $ }) { const currentDate = new Date(); const currentMonth = String(currentDate.getMonth() + 1).padStart(2, '0'); const currentDay = String(currentDate.getDate()).padStart(2, '0'); const formattedDate = `${currentMonth}-${currentDay}`; return { formattedDate }; }, }); This code step will output the formatted date with leading zeros for the month and day in the format MM-DD. 2. In the Airtable Search Records action, use the following formula in the “Formula” field: AND(IS_SAME(MONTH(BIRTHDAY), MONTH(TODAY())), IS_SAME(DAY(BIRTHDAY), DAY(TODAY()))) 3. In the “Search Value” field, use the output from the code step: {{steps.code_step_name.formattedDate}} Replace code_step_name with the actual name of the code step you added. This should search for records with the BIRTHDAY field matching the current month and day. If you still don’t get any results, please double-check the date format in your Airtable BIRTHDAY field and ensure it matches the format MM-DD.
Still not working.
The BIRTHDAY field is saved 09/28/2023.
I apologize for the confusion earlier. Since the BIRTHDAY field in your Airtable includes the year, let’s modify the code to include the year in the formatted date. 1. Add a Code step before the Airtable action and use the following code to format the month, day, and year with leading zeros: export default defineComponent({ async run({ $ }) { const currentDate = new Date(); const currentMonth = String(currentDate.getMonth() + 1).padStart(2, '0'); const currentDay = String(currentDate.getDate()).padStart(2, '0'); const currentYear = currentDate.getFullYear(); const formattedDate = `${currentMonth}/${currentDay}/${currentYear}`; return { formattedDate }; }, }); This code step will output the formatted date with leading zeros for the month and day, and include the year in the format MM/DD/YYYY. 2. In the Airtable Search Records action, use the following formula in the “Formula” field: IS_SAME(BIRTHDAY, TODAY()) 3. In the “Search Value” field, use the output from the code step: {{steps.code_step_name.formattedDate}} Replace code_step_name with the actual name of the code step you added. This should search for records with the BIRTHDAY field matching the current month, day, and year. If you still don’t get any results, please double-check the date format in your Airtable BIRTHDAY field and ensure it matches the format MM/DD/YYYY.
Let me be more clear.
The birthday would be in the past.
I want to search the AIRTABLE for a birthday that starts with the same month and the same day but not the same year.
I apologize for the confusion earlier. To search for records with the same month and day regardless of the year, you can use the following approach: 1. Add a Code step before the Airtable action and use the following code to format the month and day with leading zeros: export default defineComponent({ async run({ $ }) { const currentDate = new Date(); const currentMonth = String(currentDate.getMonth() + 1).padStart(2, '0'); const currentDay = String(currentDate.getDate()).padStart(2, '0'); const formattedDate = `${currentMonth}-${currentDay}`; return { formattedDate }; }, }); This code step will output the formatted date with leading zeros for the month and day in the format MM-DD. 2. In the Airtable Search Records action, use the following formula in the “Formula” field: AND(IS_SAME(MONTH(BIRTHDAY), MONTH(TODAY())), IS_SAME(DAY(BIRTHDAY), DAY(TODAY()))) 3. In the “Search Value” field, use the output from the code step: {{steps.code_step_name.formattedDate}} Replace code_step_name with the actual name of the code step you added. This should search for records with the BIRTHDAY field matching the current month and day, regardless of the year. If you still don’t get any results, please double-check the date format in your Airtable BIRTHDAY field and ensure it matches the format MM-DD.
That did not work either. The result was 0.
In Airtable, I created a formula field called BIRTHDAY SEARCH which stores only MM-DD.
How can I search a formula field?
I apologize for the confusion earlier. Since you’ve created a formula field called BIRTHDAY SEARCH in Airtable that stores the date in the format MM-DD, you can search for records with the same month and day using this field. 1. Add a Code step before the Airtable action and use the following code to format the month and day with leading zeros: export default defineComponent({ async run({ $ }) { const currentDate = new Date(); const currentMonth = String(currentDate.getMonth() + 1).padStart(2, '0'); const currentDay = String(currentDate.getDate()).padStart(2, '0'); const formattedDate = `${currentMonth}-${currentDay}`; return { formattedDate }; }, }); This code step will output the formatted date with leading zeros for the month and day in the format MM-DD. 2. In the Airtable Search Records action, use the following formula in the “Formula” field: {BIRTHDAY SEARCH} = "{formattedDate}" 3. In the “Search Value” field, use the output from the code step: {{steps.code_step_name.formattedDate}} Replace code_step_name with the actual name of the code step you added. This should search for records with the BIRTHDAY SEARCH field matching the current month and day. If you still don’t get any results, please double-check the date format in your Airtable BIRTHDAY SEARCH field and ensure it matches the format MM-DD.
I do not believe the Airtable Search Records is working.
In order to determine this, I created a new Search Records app and had it search in another field called Current Name and look for the word Blunt. It returned this error:
Error - Request failed with status code 422
{"error":{"type":"INVALID_FILTER_BY_FORMULA","message":"The formula for filtering records is invalid: Invalid formula. Please check your formula text."}}
I know for certain that ‘Blunt’ is in Airtable.
I apologize for the confusion earlier. It seems like the formula used in the Airtable Search Records action is causing an issue. To search for a record with the word “Blunt” in the “Current Name” field, you can use the following approach: 1. In the Airtable Search Records action, use the following formula in the “Formula” field: ```
FIND(“Blunt”, {Current Name})
That did not work.
SEARCH FIELD: Current Name
SEARCH VALUE: Blunt
SEARCH FORMULA: FIND("Blunt", {Current Name})
Error:
Error - Request failed with status code 422
{"error":{"type":"INVALID_FILTER_BY_FORMULA","message":"The formula for filtering records is invalid: Invalid formula. Please check your formula text."}}
I apologize for the confusion earlier. It seems I made a mistake in the formula. To search for a record with the word “Blunt” in the “Current Name” field, please try the following approach: 1. In the Airtable Search Records action, remove the “Formula” field. 2. Set the “Search Field” to Current Name. 3. Set the “Search Value” to Blunt. This should search for records with the word “Blunt” in the “Current Name” field. If you still encounter issues, please double-check the field name in your Airtable base and ensure it matches the field name used in the search.