Nested expressions, less columns

Ask for Help
1

I came from AppSheet world to learn this platform and now I decided to enter this community and learn the kind of things that are not just on the documents.

A little context, I already made a exact clone (functionality wise) of an AppSheet app and it works great, aside from the unsupported offline mode here.

Now, on my journey I was a little shocked that I needed to create so much computed columns to get exactly what I needed.
Template columns seems to be the most flexible ones but they are just for concatenation stuff.

Let me put this scenario:

  1. We have a Date column
  2. We need to have a custom text column with the data presented this way:
    • 2022-W15-M04-D12
  3. AppSheet uses 1 single column for this, with an expression like this one:
    CONCATENATE(
  TEXT([Date], "YYYY"),
  "-W",
  WEEKNUM([Date]),
  "-M",
 TEXT([Date], "MM"),
  "-D",
  TEXT([Date], "DD")
)
  4. My level of expertise is not high on GlideApps but I don’t find a way to do this with less than 5 columns.

Can you give me a clue on what to expect on these situations?

Also, can you give me advice to get:

  • The last day of today’s month
  • The first day of today’s month
  • Is today a workday?
  • The last day of this week
  • The first day of next week
  • The last workday of this month

Just that for the moment

PD: I’m not comparing AppSheet vs GlideApps or anything like that, but it’s important for me to know if I will be able to do things that I’m used to here so that I know if Glide is the right tool for the job in my case.

Thanks!

2

Googling, I was able to do it in just one javascript column.
You can also make it dynamic.
I think you can search a bit of what you need and use this powerful column

------ CODE ----------------------------------------------------------------------------------------------------------------
let currentdate = new Date();
var oneJan = new Date(currentdate.getFullYear(),0,1);
var numberOfDays = Math.floor((currentdate - oneJan) / (24 * 60 * 60 * 1000));
var result = Math.ceil(( currentdate.getDay() + 1 + numberOfDays) / 7);

let output = currentdate.getFullYear() + “-W” + result + “-M” + String(currentdate.getMonth()+1).padStart(2, ‘0’) + “-D” + String(currentdate.getDate()).padStart(2, ‘0’);

return output

image

4 Likes
3

Hi @JuanM, thanks for pointing that out, I noticed the Javascript column but left it out considering that JS is a whole new level for a no-code platform, I guess I’ll take it as the springboard to my coding career

4

It’s safe to say that Glide is closer to coding while AppSheet is closer to gsheet/msexcel expressions (which some people may consider coding at some level)?

5

Using the math computed column:

Last day of today’s month
(NOW-DAY(x)+15)+30
-DAY((NOW-DAY(x)+15)+30)

(@Jeff_Hager shared this one once while I was holding his beer.)

The first day of today’s month
NOW-DAY(x)+1

Is today a workday
Assuming workdays are from Monday (day 2) to Friday (day 6):
WEEKDAY(x) column then If-Then-Else column: If WEEKDAY=1 or 7 then false else true

Last day of the week
Assuming weeks starts on Sunday (which they don’t in my part of the world)
NOW-WEEKDAY(NOW)+8

First day of the week
Assuming weeks starts on Sunday (which they don’t in my part of the world)
NOW-WEEKDAY(NOW)+1

Last workday of this month
This makes me want to Google it :sweat_smile: Sorry I have no idea.

5 Likes
6

I think that each one has its context, although I have not used appsheet since glide has covered my needs with its flexibility, community and its constant growth.

I think so, glide gets a little closer to coding with the javascript column and css tricks that are here in the community.

7

Interesting you say that Glide is closer to coding: I would say that some level of logical thinking is required in Glide (at the very least useful), but other than basic math formulas, I don’t think I write a line of code using Glide. I wouldn’t be capable of it anyway :joy:

(The JS column and CSS are not necessary and used only to push the limits of the platform. Most gliders most of the time don’t need to know about these.)

2 Likes
8

too true :laughing:

9

I would probably have used math columns to determine year, month, day. I don’t know what I would have done for week (maybe a math column with mod?). And then I would have brought the information together in a template column to concatenate. Your method worked!

It’s true that while in GS/Excel, one can use an onion approach and eventually create one long formula for one column, in Glide each formula lives in its own computed column. You can, if you like, move columns next to one another and group your columns neatly in your tables by using using ‘/’ (GroupA/ColA, GroupA/ColB, etc.).

10

I get it.

That’s why I don’t compare AppSheet to Glide in this context.
What’s VERY easy on one of the platforms means “pushing the limits” on the other one.

Think about the UI for example, Glide it’s clearly miles ahead of AppSheet. The only workarounds we have are Rich Text columns with some html (no CSS support) and image columns where we could create dyamic SVGs.
Document/reports generation and webhooks, the other way around, Glide relies on third parties while AppSheet has a native way to make http calls (although no response procesing).

I wouldn’t say one is better than the other, I’m enjoying the process of learning Glide while keeping AppSheet knowledge in my head to use the right tool for the job when I need it

1 Like
11

There you have it, 5 columns. That’s what I determined with my current knowledge until the JS solution does the same with one column.

Also, would more computed columns affect performance? On AppSheet we kinda keep track of “computed columns” (virtual columns), specially when there are complex expressions inside of them.
Naturally, this leads me to think that Glide’s computed columns may affect performance.

Another one, how many tables does your biggest app has?
Because the biggest one I have on AppSheet has around 40-45 and to make sense of Glide pricing I will need to merge other ones into one big platform and that would rise the tables to 60-70

12

In the forum you will find many topics about optimizing performance, ranging from best practices in the builder (UI) to tables (in Glide, in 3rd party tools) to row ownership (to reduce the amount of data downloaded to your users’ devices and computed there).

Some builders have built gigantic apps (I’m not one of them : )

13

Thanks for your comments

Would you mind to tag someone that may have made big apps?

14

On a Glide community call which might have been about a year ago now, @Mark_Turrell blew everyone away, including members of the Glide Team, when he showed us the mindmap of his app and the roadmap.

16

I thought this would be a little more straight forward until I started messing with it. Check my math on this, but I think it’s working. I only tried a few different dates. One with a month that ends on Sunday, one that ends on Saturday, and a couple others that end on Mon-Fri. The first part is the same as your Last Day of the Month math. Then I basically subtract a day if it’s a Saturday, and subtract 2 days if it’s a Sunday using some Min/Max magic.

((NOW-DAY(NOW)+15)+30
 - DAY((NOW-DAY(NOW)+15)+30))

+

(MIN(0,6-WEEKDAY((NOW-DAY(NOW)+15)+30
 - DAY((NOW-DAY(NOW)+15)+30))))

- 

(MAX(0,2-WEEKDAY((NOW-DAY(NOW)+15)+30
 - DAY((NOW-DAY(NOW)+15)+30)))*2)
7 Likes
17

Does this takes into account when a month is 28, 29, 30 or 31 days?

18

I’m going to make this it’s own topic, see you there

19

Yes, it does. The initial part of the formula takes the date, subtracts the day number from that date, so you end up with the last day of the previous month. Then it adds 15 days to get you approximately in the middle of the month, then adds 30 days to get you approximately in the middle of the next month. Then the formula is done again, but gets the day number from that calculated date and subtracts the day number from the newly calculated date. Then end result is always the last day of the current month. The rest of the formula, with the MIN and MAX, is a trick to mathematically perform a sort of IF condition to subtract 1 or 2 days depending on if the last day of the month falls on a Saturday or Sunday.

So, to answer your question, it doesn’t matter how many days are in a month. It always figures out the last day of the month and then figures out the last workday in that month.

4 Likes
20

Deja vu math! In high school I presume. And I can’t figure this out in my head even while looking at it :sweat_smile::person_shrugging:

1 Like
21

Math was never my favorite subject, but I did OK with it to get by. Yeah this one took me a little bit to figure out. I thought it would be as simple as combining a couple of your formulas together, but that wasn’t quite working out.

You know the first part is the whole formula to calculate the last day of the month, which is also the core of the Min and Max formulas.

The Min formula will return either 0 or -1 since Saturday (weekday 7) would be the only day that we calculate something less than zero (6-7=-1). The Min will return the lesser of 0 or -1. Adding a -1 to the last day of the month gives you the prior day, which would be a Friday.

The Max formula will return either 0 or 2 since Sunday (weekday 1) would be the only day that we calculate something greater than zero (2-1=1). The Max will return the greater of 0 or 1. Then we take that result and multiply by 2. So first we will only ever calculate 0 or 1, and by multiplying by 2, we get an end result of 0 or 2. That way we can subtract 2 days from Sunday to get Friday, if the last day of the month falls in a Sunday.

3 Likes