In downloaded xlsx file Time should be change to Hour format
Question
Application Type
Reactive

Hi Team,

I have a scenario where, after we download the excel report of the web page, there is a time column which is in std format, so i need only the Hour parameter instead of the std date time format. So, what change shall i do in the excel download logic?

Solution

Hi Aditya,

My last screenshot does not show all the input params, local variables etc. so I have attached my oml for your reference. If you wish to run the application then please note:

1. You will have to add some test data in the 'ProductSales' screen because the associated Entity is empty by design when you download the oml.
2. In Reactive apps, the database saves DateTime values in UTC while the UI displays these values in user's Local Timezone. As shown in the screenshot below, the Hours in the excel file will match the database but not the UI values. Check out this forum post for more details if you wish.

Hope this helps,

AJ

HoursRecordsListToExcel.oml

mvp_badge
MVP

Hi Aditya,


I don't think you can do this by using the built-in RecordListToExcel functions, but you can do that with Advanced Excel forge component.

The component features a comprehensive documentation and demo which will help you understand how to achieve your use case using the component.


Kind Regards,
João

Yeah, Actually I have used the built in RecordListToExcel function. So, no solution for this using the builtin function?

mvp_badge
MVP

Hi Aditya,

Does the Attribute that is exported to the Excel have Time as Data Type, or Date Time?

Hi Kilian,

Date Time data type is there to the attribute

mvp_badge
MVP

Have you thought of changing the Data Type to Time? Assuming you have use a Structure and not an Entity directly.

Hello Aditya,

The way I understood your ask, there may be other ways, but here's one way:

1. Create a structure for the database entity with the difference that instead of the original DateTime or Time attribute, replace it with say, an 'Hour' attribute.

2. In a Server action, loop through the original data source and assign all values from the original list to the new list except, in the case of the DateTime/Time value, you assign only the "H" - Hour to the new list using for e.g. 

FormatDateTime(ProductSalesList.Current.Date, "H")

3. Now that you have a new list with the Hour attribute, create the Excel binary and output the binary back to the client action for download.

Hope this helps,

Regards,

AJ

mvp_badge
MVP

Though I agree with the general approach, I have some thoughts:

  • It's not clear Aditya directly uses an Entity instead of a Structure. I asked her above to clarify but she hasn't answered yet;
  • There's no need to loop over the list. You can assign one list to the other and use a mapping to map the attributes. This is faster and makes for less code;
  • I'd first try to use a Time data type in the Structure instead of a Date Time. If that doesn't work, I'd use Text and a FormatDateTime like you proposed, but it wouldn't be my first approach.

Yes I am using a Structure. 

Can u please elaborate your second point, There is no need.......one

Thank you Kilian! Totally agree on all points.

Aditya, on the second point, I should have just mapped the list to list directly as shown below (performant) instead of looping through (non performant)

I usually just map lists when the data types are the same but for some reason I thought I couldnt do so if one of the attributes had different data types. Glad Kilian kept me honest.

Hi AJ,

I am trying to apply the same as you mentioned above, but How do we assign a variable in server action as it is not under the server actions variables. means only one input parameter of list is there.

mvp_badge
MVP

Hi Aditya,

You can either type the name by hand, or open the Expression Editor (either by double clicking or selecting it from the dropdown). The reason why it's not in the list of suggestions is because it has a different data type.

Solution

Hi Aditya,

My last screenshot does not show all the input params, local variables etc. so I have attached my oml for your reference. If you wish to run the application then please note:

1. You will have to add some test data in the 'ProductSales' screen because the associated Entity is empty by design when you download the oml.
2. In Reactive apps, the database saves DateTime values in UTC while the UI displays these values in user's Local Timezone. As shown in the screenshot below, the Hours in the excel file will match the database but not the UI values. Check out this forum post for more details if you wish.

Hope this helps,

AJ

HoursRecordsListToExcel.oml

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.