Dynamic column names in formulas

[Otto-d172]

Hello! Is there a way to dynamically reference a column name in formulas?

I'm using FINDROW() to get rowlinks in table A from table B, however, I'd like to get a specific column based on yet another formula/condition.

In the following example: Dynamically Retrieve a Rowlink's Column I'd like to make the "Actions" column name value in step 6.- a formula so that the column selected changes based on a certain condition. In my case the current weekday.

Thank you!

[Alyssa]

Hi @Otto-d172

Happy to help! If you're looking to add another condition to your FINDROW formula, you would use the AND operator.

Building on the same example that you linked, the formula would look like:
=FINDROW(Action, "Action[Name]=% AND Action[Due]=TODAY()", "Mateo")

You would want to have another column in the table ("Due" for this example), in which you can check to see if it matches the current day (TODAY()).

I'll note that this formula will only return the first row that meets these conditions. If you instead are looking to retrieve all rows that meet the criteria, you'd write the same formula with FILTER instead.

Let us know if that helps, and if you have any other questions.

[Otto-d172]

Thank you, @Alyssa

In my case, FINDROW() is finding the right row for me in table B. What I need is to select a specific column name once I fetch the FINDROW() results (i.e. rowlink). This column name, though, it's another formula/condition that changes based on the day of the week. It would be something like this in your example:

=FINDROW(Action, "Action[Name]=% AND Action[Due]=TODAY()", "Mateo")[formula/condition-here-to-select-the-column-I-need]

I'm building a calendar of sorts where users can click a drop-down in a calendar-like table where they can choose the on-call group for every hour of the week. This table has hours of the day (leftmost column) and day of the week (top row) and a cell.

Maybe there's an easier way to do this ?

Thank you!

[Alyssa]

Hi @Otto-d172,

Got it! Sounds like you'd like your users to be able to select dates or times and then have data return based on those selections. If that's the case, that would be built in the app rather than the table. (Dynamic data dependent on user selections would be in the app and refer to on-screen variables).

Here's a good example that demonstrates this, in which I built an app that allows data to be displayed based on selected dates: Not able to create Employee - Manager relationship - #7

You can use the same concept in that example to only show team members that are assigned to be on-call for that given time frame (you can make it based on Time & Date rather than just Date, if you want to specify by the hour).

Once you take a look at that example, let me know if we can assist further.

[Otto-d172]

Thank you for the follow-up, @Alyssa,

I'm trying to do something slightly different from what you referenced, however, I understand that I could get the result that I need from the app itself, not the table. Here's the ERD draft I'm working with as a reference:

One of my goals is to get the on-call group name ID (onCallGroupNames[groupNameID]) based on the time of the day and day of the week, programmatically.

I was hoping I could create a summary table where I could create a filter that would give me the row from the onCallRotation with the current hour (which is working fine) and select the column matching the current day and query that summary table directly, hence my initial question to make the column name in a filter a variable (such as the day of the week).

Since I need to get the groupNameID from the onCallRotation table through a Lambda function, it sounds like my best option would be to query the table directly, get the row I need based on the hour of the day, and then parse the results to get the column that matches the current day of the week to get the right groupNameID. Can you please confirm?

Also, if you have any advice on how to best handle calendar like scenarios where users need to input information on recurring blocks of time (not specific dates), e.g. Monday-Friday (Weekdays) from 8:00pm-2:00am, I'd appreciate it. I'm creating a table to accomplish this functionality (onCallRotation), but I'm unaware if there's a more efficient way.

Thank you!

[Alyssa]

I see, @Otto-d172, thanks for those additional details!

This is how I would build an on-call summary in Tables for a recurring schedule based on days of the week:

First, you'll want to have a table that stores the weekdays. The WEEKDAY() function returns a number to represent a day of the week. The purpose of the table is to help display the day in a form we recognize better (i.e. "Tuesday" or "Wednesday"):

I then created a table of Shifts that could be associated to any day. I'll note that I created a couple of additional columns that calculate if the current time is before or after a shift start or end. That will be relevant to the summary table that we'll get to:

Note: The calculations of the TRUE or FALSE checks I added are based on a calculated time to my time zone (PST). Honeycode time is in UTC, so refer to this article on converting time: UTC & Time Zone Conversion.

The formula for the current hour (converted to PST): =HOUR(NOW()-8/24)
Check to see if start is after current hour: =[Current Hour]>=([Start]*24)
Check to see if end is before current hour: =[Current Hour]<([End]*24)

I then created a Schedule table where I assign a "Group" to a shift. Each column is a rowlink to another table (you'll want to have a Groups table where you perhaps assign team members):

Finally, the Summary table shows the current time and which group is assigned to the current shift:

For the formulas for each column:

The "Current Weekday" column checks the current weekday and displays that name of the day based on the Weekday table:

=FINDROW(Weekday,"Weekday[Day Num]=WEEKDAY(TODAY())")

The "Current shift" column checks the current time and looks for the shift where the time is after the start time but before the end time of a shift (refer to the Shift table I set up):

=FINDROW(Shifts,"Shifts[After Start]=TRUE AND Shifts[Before End]=TRUE")

And the assigned group checks for the group in the row of the Rotation Schedule where the current day and shift match:

=FINDROW(OnCallRotation,"OnCallRotation[Weekday]=OnCallSummary[Current Weekday] AND OnCallRotation[Shift]=[Current shift]")[Group]

Now, in an app, you can set up a screen that displays the data of your Summary table:

For your last question, if you wanted to have users input information for each recurring shift, you can add additional columns to the rotation schedule table (i.e. a "Notes" column), and set up your app to allow for edits of these details. (i.e. on a Details screen. If your schedule is set up in your tables, you can easily set up an app with a list and details screen for the shifts using the App Wizard). You're also able to modify the UI of the app to create a look more of a calendar (rather than a listed schedule), but that's more of a visual preference.

If you'd like to further explore integrating Honeycode with other services that may meet any other calendar / scheduling requirements, check out Honeycode's two-way integration with Zapier and AppFlow to connect your data and app with another service.

Hope all this helps! Let me know if you have any other questions.

[Otto-d172]

Hello @Alyssa ,

Your solution works great for my use case and gives me ideas on how to better break down data relationships for other data models I'm building in HC.

Thank you for taking the time to work through this with me, I appreciate it!

[Alyssa]

Great! I'm happy it helps you see how it all works together in Honeycode, @Otto-d172

And of course! Feel free to reach out to us again if you have any other questions.