Day of Week lookup

For HanDBase related topics that are not specific to one platform.

Day of Week lookup

Postby glenwf » Fri Dec 06, 2013 9:40 pm

I see the program posted "Day of Week", but I can't see all the calculations entered in the formula because it is too long and I can't get into it to scroll through it. Can you list the calculations for me?

Also, can this "Day of Week" set be used as an outside source to another application to ask the day of week and have it return the answer?

I tried to email the author, but something there fails for me (I'm on a new computer and I don't have all the quirks worked out yet).

Thanks.
glenwf
 
Posts: 170
Joined: Wed May 20, 2009 5:34 pm
Location: Roseville, CA

Re: Day of Week lookup

Postby Brian_Houghton » Sat Dec 07, 2013 10:50 am

Hello,

Please take a look at the following article, as it will help you out:

http://ddhsoftware.com/knowledgebase.html?read=196&UID=

Sent from my Nexus 7 using Tapatalk HD
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: Day of Week lookup

Postby Ian Hinton » Fri Jan 03, 2014 2:07 pm

I've just read the article that was posted here. I'm also trying to find a workaround for this feature, but am encountering a problem when trying to create the name-of-the-day.

In the first place, my calculation of the number of the day of the week is working correctly. I'm using a more simple formula than the one mentioned in the article:
((Fn + 4)Mod7)+1
Fn represents the field with the date. Please bear in mind that my week starts on Monday, not Sunday (I live in Spain). For anybody interested, the formula can be adapted for Sunday as first day of week, by substituting the 4 with a 5, thus: ((Fn + 5)Mod7)+1

The resulting number of day-in-the-week is then used in another field that is of Relationship type. This field then tries unsuccessfully to bring up the day of the week from another file that contains thae following data:
1 Mon
2 Tue
3 Wed
etc...
The number of day is defined here as an Integer field.

After testing several things, I've realised that the Relationship field brings up a day-of-the-week only when it is using a number that has been typed into another field. Apparently it doesn't work with a Calculation field. I've tried to find another work around to this problem, but no luck. Any clues anybody?
Ian Hinton
 
Posts: 4
Joined: Fri Dec 20, 2013 11:25 am

Re: Day of Week lookup

Postby dhaupert » Mon Jan 06, 2014 2:35 pm

Does your calculation field exist before the relationship field in the field order you are using? I ask because HanDBase always looks up and calculates field values in the order of the field order, and often times customers don't necessarily have them in the correct order for being calculated. Let me know if they are!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Day of Week lookup

Postby Ian Hinton » Mon Jan 06, 2014 5:14 pm

That is something I didn't know, but I've just checked it, and the calculation field is "accidentally" just before the relationship field:
F5 ...... Due date ... Date field
F19 ..... Day # ....... Calculated field: (((F5+4)Mod7)+1)
F20 ..... Day ......... Relationship field

I can see that Day # is calculated correctly in every record of the database. If I edit a record and tap on the button for Day, the app calls up my Weekdays file, but without the listing of weekdays. At the top I can see Weekdays (0/7). So the file seems to be linked correctly, but Day # seems to not be valid for some reason. The Weekdays files contains the 7 days of the week numbered from 1 to 7 (not 0 to 6).
Ian Hinton
 
Posts: 4
Joined: Fri Dec 20, 2013 11:25 am

Re: Day of Week lookup

Postby aa6vhDave » Tue Jan 07, 2014 11:46 am

I created a lookup table that contains the day name based on the day of week integer (the date mod 7 value). Note that if you do no other "adjustments", Friday comes up as day zero.

In the computational field in the original database, I defined the output as Integer.

In the lookup day name table, I specified the integer day value (with entries from 0 to 6), along with the day name.

It did not work.

So in the day name lookup table I changed the integer day value from a column type of number, to a column type of text (still containing the integer day value).

And now it works!

(now simply use a relationship column in the original table to look up the day name in the day name lookup table.)
aa6vhDave
 
Posts: 53
Joined: Mon Aug 19, 2013 1:42 pm

Re: Day of Week lookup

Postby Ian Hinton » Tue Jan 07, 2014 1:22 pm

I've just changed the field type from Integer to Text in the day number of the lookup day name table, and it now works correctly. Thanks a ton for your help. :D I'm surprised I didn't get this advice from the author. :( I've spent hours trying out all sorts of things.
Once again, thank you very much aa6vhDave.
Ian Hinton
 
Posts: 4
Joined: Fri Dec 20, 2013 11:25 am

Re: Day of Week lookup

Postby dhaupert » Tue Jan 07, 2014 3:24 pm

Hi Ian,

I'm glad he was able to help you as well. Would have gotten there at some point, but probably would have had to see your databases to get where aa6vdave got without them, so kudos to him!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Day of Week lookup

Postby aa6vhDave » Tue Jan 07, 2014 3:44 pm

You are quite welcome.

Now for extra credit:

When I created the day name lookup table, I created three day name columns, one with the two letter abbreviation of the name (e.g. "Fr"), one with the three letter abbreviation (e.g. "Fri"), and the last with the full name (e.g. "Friday"). As I use this lookup table for several different tables, I can use the two or three letter abbreviation when the day name is displayed in a list view, and the fully spelled out name if used on a form with lots of extra space. When setting up the relationship column, I just pick the appropriate column name in the lookup table for the day name format that I want.
aa6vhDave
 
Posts: 53
Joined: Mon Aug 19, 2013 1:42 pm

Re: Day of Week lookup

Postby Ian Hinton » Tue Jan 07, 2014 4:30 pm

I was doing something similar: single letter (Spanish language) and three letter versions (of Spanish and English language), making a total of three different versions of the weekday names.
Thanks again.
Ian Hinton
 
Posts: 4
Joined: Fri Dec 20, 2013 11:25 am


Return to HanDBase General Discussion

Who is online

Users browsing this forum: Google [Bot] and 1 guest

cron