I am not sure about the Lobby question, but a quick word on dates (as related to your Question 2)…

I am not sure why you are using NVL() on TRUNC(SYSDATE), as SYSDATE will never be null. And when comparing dates you can simply use a equals sign. For example, this gathers all invoices from yesterday:

SELECT * FROM Man_Supp_Invoice WHERE TRUNC(Invoice_Date) = TRUNC(SYSDATE - 1)

Straight date comparison with an equals sign, the same way you would directly compare strings or numbers.

Another handy function you might want to familiarize yourself with is TO_CHAR(), as it is what is used to convert numbers and dates to strings (which you can then compare). For example, I could do what I did above via TO_CHAR with something like this:

SELECT TO_CHAR(Invoice_Date, 'MM/DD/YYYY') AS ToCharDate, MSI.*
    FROM Man_Supp_Invoice MSI
    WHERE TO_CHAR(Invoice_Date, 'MM/DD/YYYY') = TO_CHAR(SYSDATE - 1, 'MM/DD/YYYY')

Note that if I used dissimilar format masks in TO_CHAR() on either side of the comparison, I would not get any matches. The default format mask appears to be DD-MON-YY, though that may be controlled by regional settings on the Oracle instance or client side (not sure).

TRUNC() and TO_CHAR() can end up doing the same thing for you: isolating the date portion of what is actually a date/time field, for both displaying and comparing dates.

Unfortunately, I am not able to use an expression in the Default Value of a Lobby data field (with regard to your Question 1). So, you have a good question there -- how can one change the format of the field a user will enter when it comes to dates? I have not created any Lobbies using a date field as a Data element, but probably will at some point. It would be good to know how to control the formatting on that…

Thanks,

Joe Kaufman

@sutekh137 thanks for pointing that sysdate will never be null, i soon realized after posting that. I tried date mask in my condition and i can get results  when user input a date but not with #Today# , the default value. so far i am getting either or. sysdate (default)or user entered date.

When you say you cannot get results, is that because when you try to match on date for a user-entered value you use the mask “MM/DD/YYYY”, but #TODAY# is coming back as “YYYY-MM-DD”?

You are going to have to match by using the counterpart to TO_CHAR(), called TO_DATE(). And you will need to check both formats “MM/DD/YYYY” and “YYYY-MM-DD”. Here is an example with hard-coded dates where I am looking for AP invoices across Dec 6 and Dec 7:

SELECT TO_CHAR(Invoice_Date, 'MM/DD/YYYY') AS ToCharDate, MSI.*
    FROM Man_Supp_Invoice MSI
    WHERE Invoice_Date = TO_DATE('12/06/2021', 'MM/DD/YYYY')
    OR Invoice_Date = TO_DATE('2021-12-07', 'YYYY-MM-DD')

If the hard-coded string literals ‘12/06/2021’ and ‘2021-12-07’ were replaced with the Lobby page parameter DATE_ENTERED, then you will get a match as long as the date matches and is in one format or the other.

This should have the advantage of not only making the default #TODAY# work, it means that user’s can enter a date parameter manually in MM/DD/YYYY or YYYY-MM-DD format and it should still find matches.

Hopefully you can see what I mean by using TO_DATE() and an OR on your WHERE clause…

Thanks,

Joe Kaufman