Math Date function use

[David_Gabler]

I have a control record where I get the current date using a MATH column.

Then I do some other columns to whittle it down to just the Month and Day (think of a birthday which repeats every year, so the year is not significant), then I set up a relation between that day and a Trimmed Lowercase field in my list of “Feasts”. The relation seems to work.

Then I set up some columns in my user table, because that table seems to be able to be referenced everywhere (unlike my Control Table). That gives me my Feast ID. So far so good.

But when I go to the Feast table, any attempt to user the Feast ID in the user table (while it seems to work in the table itself), does not seem to work in the Filter or “visible” for the tab kind of tests. You can see “user feast relation” is “not empty” in one record, but no records show up in my list when I reference “not empty” on that column. I am actually going to have that a detail screen, but used a list for illustration purposes.

This may seem to be a convoluted way to get here, but I had originally tried just matching my Trim LC Date column with the Trim LC Date column in my user record, and that didn’t give a match either, so that’s when I started setting up relations to see if I could work around that. The two dates obviously “match”. “january 24” in the feasts table and “january 24” in the users table. Is there some issue with the fact that it comes from a math column and not a text column? That’s why I thought I could work around it by using relations.

Why the control record? I figured it would be faster (if I have 3000 users) to do all the computed columns in one row and use the final results in my user table rather than have all the computed columns on every user row. That’s just my way of thinking because I don’t know if Glide only does the computed columns for the currently logged in user, or for all users in the table (like I see in my editor).

However, the results (or lack of results) are the same if I move all the computed columns to the user table – it still does not “match” correctly or filter correctly.

[ThinhDinh]

@David_Gabler

Then I do some other columns to whittle it down to just the Month and Day (think of a birthday which repeats every year, so the year is not significant), then I set up a relation between that day and a Trimmed Lowercase field in my list of “Feasts”

Firstly I think you can have just one column to get to the trimmed month & day here, instead of 2. Are you using experimental code column for this? Or some JavaScript?

For your filter, what is your setting for this relation?

I’m thinking you could have the setup like you did originally, just inside the Users table, then use that same relation (set it to multiple match) as your source to display, not having to filter anything else.

[David_Gabler]

@ThinhDinh

Firstly I think you can have just one column to get to the trimmed month & day here, instead of 2. Are you using experimental code column for this? Or some JavaScript?

Well, I was using the Lowercase and Trim Whitespace “text” functions. But I have removed all that – all of it was just workarounds to try and make it work. I’ve reduced it to its simplest components.

Here, I have moved everything to the User table:

And in my Feast table, I have these columns (where Month Day is a lookup based on the User Feast Relation):

Well, and I think I can see why the filter fails – here I am simply trying to display “Month Day” in my LIST, to verify what it is, and you can see that Month Day (the lookup) does not display… It also does not work as part of the filter.

[Darren_Murphy]

I’m not sure that I have a solution for you, but I have a couple of observations and a suggestion.

Firstly, I have a niggling feeling that your math/split text/single value approach might be tripping things up. I’ve been playing around trying to replicate your setup, and it “works”, but one thing I noticed is that when I take the Single Value month/day column and display it in a component, it appears as mm/dd/yyyy, even though it looks like “January 25” in the Data Editor. This makes me suspicious that what you see in the data editor is not the “true” value.

So, here is something to try: take that Single Value MonthDay column, and pass it through a template column, using it as a replacement value. This will force it to be a string, and eliminate the concern above. You’d need to do that in both tables.

My other comment is that I don’t really see the point of the relation/lookup that you have in your GF Feasts table? Couldn’t you just use a Single Value column to take the Month Day value from your GF Users table and apply it to all rows? Actually, for that matter - if all you’re wanting to do is filter the records based on the current date - why not just do everything in the GF Feasts table? (I guess there is a larger context that I’m missing).

[Uzo]

if you are trying to filter the math column based on text columns… it will not work… convert all columns to text first… i.e when you are extracting a month from the date, using a math column… it will be still a full date

[David_Gabler]

@Darren_Murphy

if all you’re wanting to do is filter the records based on the current date - why not just do everything in the GF Feasts table?

You weren’t missing any context, except that when I have “universal” values, that I may want to use against other tables, my tendency is to put them in a central location --in this case the “user” table, since it is the only table Glide will always let me access. At any rate, when I had these columns set up in the user table, Glide did not do the “is” correctly. But when they are both in the same table, Glide does it right, it seems. I will have a go at your suggestion to use a template column tomorrow. It’s too late tonight, but you can see that it works if it’s all in the same table. I really need it in the user table because I may/probably will implement other functions concerning the date, and it should be central.

[David_Gabler]

@Uzo

convert all columns to text first

By using a template column? Because I would have thought using a text function like “lowercase” and “trim whitespace” would have converted it to text. No?

[Uzo]

yes, the template is a good way to do that… just add an empty space before or after… for all values that gonna be used in filters… this way all of them will be in the same format…
Glide is not working like Google Sheets… each format has an identifier that affects filters.

[Darren_Murphy]

@David_Gabler

except that when I have “universal” values, that I may want to use against other tables, my tendency is to put them in a central location --in this case the “user” table,

ah, yes. That makes perfect sense, and I do exactly the same thing. In fact, in most of my apps I will have a whole series of now-based math columns in my user profiles table, that are used as a single reference point. Things like “this year”, “last year”, “first day of this week”, “first day of this month”, etc, etc…

I find this to be a good approach, and avoids a lot of unnecessary duplication.

When it comes to building relations based on dates, I find that the most important thing (as @Uzo pointed out) is to ensure that I’m using text based representations of the dates. So in almost all cases, the two columns I use to form the relation will both be template columns.

My general approach is:

• Use math columns to extract the component parts that I need. For your use case, this would be Day(Date) and Month(Date) respectively
• If necessary, convert numeric values to their text equivalents. For example, Month(Date) returns a number, so if you want the month name that requires some extra massaging (see below).
• Use a template column to stitch the component parts back together.

For converting things like Month(Date) and Weekday(Date) into text representations, there are a couple of approaches. What some people do is use if-then-else columns: if 1, then Jan, if 2 then Feb, if 3 then Mar, etc. I don’t do this, because it’s way too much effort for my liking - and I’m lazy

What I do instead is create a Lookup table somewhere in my app that has columns that list the month and weekday names…

And then I use single value columns to obtain the appropriate month or weekday name based on the output of the math column.

So for example, to get the current date into the format you are using, I’d have 4 columns:

• Math column to get the month number (-1, because single value columns use zero based indexes)
• Math column to get the day number
• Single Value column to convert the month number to the month name
• Template column to join the day number and month name

It should be noted that the above can (in theory) be done using a single column with the Format Date plugin. BUT… I have found that plugin to be buggy and unreliable. So I don’t use it, and I don’t recommend using it.

Anyway, take all the above as you wish. Feel free to use any of it, or all of it, or none of it

[Uzo]

@Darren_Murphy wow! are you writing a book?

[David_Gabler]

@Darren_Murphy

Feel free to use any of it, or all of it

Thank you so much! I have begun to implement this all.
Thank you to everyone who contributed to this topic.

I have to confess I program in a language in which data type is not such a big deal, because if we reference a numeric and a character array in an expression, the program @ runtime does the internal conversions necessary for it to work.

[Darren_Murphy]

@David_Gabler

I have to confess I program in a language in which data type is not such a big deal, because if we reference a numeric and a character array in an expression, the program @ runtime does the internal conversions necessary for it to work.

Sounds like Perl

[David_Gabler]

Not Perl – Sunbelt’s Visual PL/B (a derivative of Datapoint’s Databus, which was a derivative of COBOL). It’s become a fully modern language, with .NET, etc. But it is a bit obscure (as in no one has ever heard of it).

[Jeff_Hager]

RPGLE and .NET are my bread and butter by day. Glide is my escape by night.

[David_Gabler]

@Jeff_Hager

RPGLE

Have not heard that name in a LONG time!

[Jeff_Hager]

It’s still alive and kicking. The green screen as well. Hard to find programmers that know it these days.

[David_Gabler]

Same with PL/B. Years ago I worked alongside an RPG programmer – I wrote the MS-Dos character-based screens and her job was to make the RPG programs work the exact same way as mine (tabbing and all). She was in a constant state of frustration (LOL) because RPG and DOS handle keyboard input in different ways (at least in those days).

[Jeff_Hager]

Hehe, yeah it’s different, but I enjoy it. Been doing it for over 20 years. There’s pluses and minuses, but for me it’s a lot easier to work with compared to web development.