How to show the first next date of two given dates

How to show the first next date of two given dates

  

Hi,

Currently I am looking how to display the first next date out of two given dates. The date Data Type is Text, because this is imported from an external data base with a different SysDate. 

Format of the dates: 22/11/2018 (dd/mm/yyyy) - Text Data Type

So I have two dates and I want to show only one date that is coming up first.
Does anyone have any ideas how to manage this? 

Many thanks in advance! 

Hi Martijn,

Not sure if I'm understanding your problem... You have a string with two dates on it and you only want to show the first one? If this is your problem have you tried the action "String_Split" (Text extension)?

Best Regards,

Hi Martijn, 

Have you tried to use the build in function:

TextToDate()

 to get the correspondent dates in a date format a use those values to calculate and display whatever you need? 

Just an idea, hope it helps

Maria da Graça

Ruben Meireles wrote:

Hi Martijn,

Not sure if I'm understanding your problem... You have a string with two dates on it and you only want to show the first one? If this is your problem have you tried the action "String_Split" (Text extension)?

Best Regards,

Hi Ruben,

No I have 2 attributes in an Entity with 1 date each. They are delivery dates. And I only want to show the date of the first delivery. 


Maria da Graça Peixoto wrote:

Hi Martijn, 

Have you tried to use the build in function:

TextToDate()

 to get the correspondent dates in a date format a use those values to calculate and display whatever you need? 

Just an idea, hope it helps

Maria da Graça

Hi Maria,

Thank you for your reply. I was thinking about converting, but that does not help me any further unfortunately. The thing is that I can not find a function of the "smalles date", like the Numeric function 'Min'.  I tried to convert the dates into decimals and use the Min function, but that seems hopeless. 



Martijn de Pijper wrote:

Maria da Graça Peixoto wrote:

Hi Martijn, 

Have you tried to use the build in function:

TextToDate()

 to get the correspondent dates in a date format a use those values to calculate and display whatever you need? 

Just an idea, hope it helps

Maria da Graça

Hi Maria,

Thank you for your reply. I was thinking about converting, but that does not help me any further unfortunately. The thing is that I can not find a function of the "smalles date", like the Numeric function 'Min'.  I tried to convert the dates into decimals and use the Min function, but that seems hopeless. 



If you've actually managed to convert input to Date data-type, you can compare them with DiffDays(). 

Documentation: https://success.outsystems.com/Documentation/10/Reference/Logic/Built-in_Functions/Date_and_Time_Built-in_Functions


Sam


Hi Martijn,

There is a function to convert text to date (TextToDate), but your data is not in the right format. You can check it here.

You can build a new date using the following functions:

  • substr to divide your string, and then TextToDate
  • substr to divide your string, and then NewDate .

Then you can use DiffDays(Date1,Date2)

HI Martijn,


Yes you can convert those two attributes to date using the function TextToDate(). 
Then you can compare those two dates with CurrDate() using DiffDays() / DiffHours() / DiffMinutes() etc.
So you can get the result whichever is lower from those two results.
For example,
DiffHours(CurrDate(), DateVariable1) = 5
DiffHours(CurrDate(), DateVariable2) = 7

In above case, DateVariable1 is the date you want.

Regards,
Palak Patel

Hi Margarida, 

If the date is in the server´s environment configuration the TextToDate function should also work, but yous is a safer solution. 

Regards, 

Graça

Solution

Ok, I'm not sure why everyone insists on using DiffHours, DiffDays or whatever, but you can just compare two dates to decide which one is smaller. Given that Martijn's dates are in text form, the first step is to convert them. Given the pattern dd/mm/yyyy, this is as simpel as:

NewDate(TextToInteger(Substr(DateText, 6, 4)), TextToInteger(Substr(DateText, 3, 2)), TextToInteger(Substr(DateText, 0, 2)))

Then, the smallest date (i.e. the one coming first) can be retrieved by:

If(Date1 < Date2, Date1, Date2)


Solution

Hi,

Is not correct he cant use the TextToDate because of the format dd/mm/yyyy. Everything depends on the format that is configured on Environment Configuration. If the format configured is DD-MM-YYY you can use it and than just check which one is smaller as Kilian suggested.

Regards,

Marcelo

Thanks everybody for your quick response. 

I was puzzling around and have to say that Kilian had the answer that suited the best. I tried to work it out with variables in the beginning (DateX and DateY). I filled these variables in the OnChange client action. Then I used the Expression to fill the given If statement Kilian mentioned. 






It worked, but it only showed 1 specific date for all of the results. 

Then I created the expression without the variables, but just written out the statement completely. And the results are just as wished! 

The expression i used:


SyntaxEditor Code Snippet

If(NewDate(TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryRegularDate, 6, 4)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryRegularDate, 3, 2)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryRegularDate, 0, 2)))
 < NewDate(TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryPromotionDate, 6, 4)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryPromotionDate, 3, 2)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryPromotionDate, 0, 2)))
, NewDate(TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryRegularDate, 6, 4)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryRegularDate, 3, 2)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryRegularDate, 0, 2)))
, NewDate(TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryPromotionDate, 6, 4)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryPromotionDate, 3, 2)), TextToInteger(Substr(GetProductsByEAN.List.Current.Delivery.DeliveryPromotionDate, 0, 2)))

Thanks again everybody! 

Hi Martijn,

Great to see it worked. Please understand why using Variables doesn't work: there's only a single value for a Variable, but since you have a Table Records Widget displaying each element of a List, you indeed need to use the Current of the List.

That said, there's two things you can do to improve your code:

  1. You are using GetProductsByEAN in the Expression. I assume that that you bind your Table Records to GetProductsByEAN.List? In that case, don't use the List directly, but reference the Table Records instead, e.g. TableRecords1.List.Current. etc. This is a best practice.
  2. Create a Function (i.e. an Action with the Function Property set to Yes) that does the conversion of your text date to an actual Date, and a Function that returns the smaller of two dates. This will greatly enhance the readability of the code. You'll be left with:
MyMinDate(TableRecords1.List.Current.Delivery.DeliveryRegularDate, TableRecords1.Current.Delivery.DeliveryPromotionDate)

With something like this in the MyMinDate Function:

if(MyTextToDate(MyDate1) < MyTextToDate(MyDate2), MyTextToDate(MyDate1), MyTextToDate(MyDate2))


Many thanks Kilian! 

You're most welcome. Happy coding!

Kilian Hekhuis wrote:

Ok, I'm not sure why everyone insists on using DiffHours, DiffDays or whatever, but you can just compare two dates to decide which one is smaller. Given that Martijn's dates are in text form, the first step is to convert them. Given the pattern dd/mm/yyyy, this is as simpel as:

NewDate(TextToInteger(Substr(DateText, 6, 4)), TextToInteger(Substr(DateText, 3, 2)), TextToInteger(Substr(DateText, 0, 2)))

Then, the smallest date (i.e. the one coming first) can be retrieved by:

If(Date1 < Date2, Date1, Date2)


To expand on Killian's explanation of usage and the formula;

This logic can be applied to the Table List on the screen in an expression directly or as a custom attribute function in the aggregate.

A User-Function can also be used in both of those scenarios.



Hi Ouen,

Perhaps I'm misreading what you wrote, but it is not possible to use user functions in an Aggregate. Only functions that are available in the database engine itself can be used.

But yes, the conversion from text date to actual date can be done in the Aggregate (as NewDate and Substr are afaik available in an Aggregate), but I'd consider this an advanced usages, and I deliberatly chose not to go that route, since the OP seems a novice developer.

Killian,

As long as the user function does not contain any database lookups or makes use of any functions that use of any database lookups then it can be used in the custom attribute as a function.

The usage scenario is more advanced but I think there is value to anyone who is learning and growing on the platform.

Ouen

Hi Ouen,

I think you are confusing two things. Yes, you can use User Functions in the formula of a Calculated Attribute, but you can only use "static" functions, i.e. those that return their output independent of the queried data. The Platform first executes the Function, then assigns the output to a database variable, then executes the query.

If you try to use an Entity Attribute as input to the Function, you get the following error message:

So in the case of this topic, you can't use the "MyTextToDate" Function I proposed above in the Aggregate.

Kilian Hekhuis wrote:

Hi Ouen,

I think you are confusing two things. Yes, you can use User Functions in the formula of a Calculated Attribute, but you can only use "static" functions, i.e. those that return their output independent of the queried data. The Platform first executes the Function, then assigns the output to a database variable, then executes the query.

If you try to use an Entity Attribute as input to the Function, you get the following error message:

So in the case of this topic, you can't use the "MyTextToDate" Function I proposed above in the Aggregate.

Thanks, Killian.