Display Only "Time" in a GI, not Date.

darylbowman this is perfect! Thank you so much!

That’s really clever, but if they don’t have field services, then I don’t think they can use this trick.

​@bodiec you may need to write a formula that looks like below. Replace [Customer.CreatedDateTime] with your field and adjust the -4 value to match your time zone. It may need to be -6 for example.

=IIF(HOUR(DateAdd(([Customer.CreatedDateTime]), 'h', -4 ))<=12,PADLEFT(CSTR(HOUR(DateAdd( ([Customer.CreatedDateTime]), 'h', -4 ))),2,'0')+':'+PADLEFT(CSTR(MINUTE(DateAdd( ([Customer.CreatedDateTime]), 'h', -4 ))),2,'0')+ ' AM',PADLEFT(CSTR(HOUR(DateAdd( ([Customer.CreatedDateTime]), 'h', -4 ))-12),2,'0')+':'+PADLEFT(CSTR(MINUTE(DateAdd( ([Customer.CreatedDateTime]), 'h', -4 ))),2,'0')+ ' PM')

This will not adjust for daylight saving time. If this needs to be done, the formula gets a little more complicated.

darylbowman wrote:

bodiec wrote:

Does anyone know if it is possible to display just the time of a transaction on its own in a GI?

I’m gunna be honest, I knew it was possible, but there were limitations to the way I was initially going to suggest, so I spent a little time trying some things, and I discovered a much better way:

 

 

Here’s how:

1. Add the FSAppointment DAC to the Tables / Sources tab (no need to join it in Relations)
2. Add your date/time field in the Results
3. In the Schema column, paste this: FSAppointment.ActualDateTimeBegin_Time
4. Add a caption to the Caption column (or else it will display as ‘Actual Start Time’ [you may need to add the Caption field from the column chooser; I think it’s hidden by default])

The end result should look something like this:

 

Thank you to both bwhite49 and darylbowman both of these options were able to display the time correctly for me.

However, in both cases, the system seems to not like these displays because I cannot use them to filter,

In Bwhite49’s example, the filter allowes me to select a time, but then it overrides and specifies today date on that time in the filter. 

And for darylbowman’s example, the filter doesn’t recognize a time format at all and only lets me type a free text in the filter.

I think I can still use these however I think I need to create a new field thats a checkbox, that will be checked if the time stamp is outside the specific time zone.

Does anyone know if that is possible? Can I write a code for the box to check if the time to be between 1:30 am and 4:00am?

I tested it in an instance without field services enabled. It has no bearing.

In theory, you could add start and end parameters with the same Schema and set their default values to be your limits. Then compare the values. I’m not sure if the underlying date would come into play or not.

Try this formula as the date value with the previous schema trick. It should make all the dates 1/1/1900. In my test, it does allow filtering on the column. It should also remove the underlying date component if it causes issues elsewhere.

=DateAdd([ARInvoice.LastModifiedDateTime],'d',DateDiff('d',CDate('1/1/1900'),[ARInvoice.LastModifiedDateTime]))

Disregard. It does allow filtering, but the filtering is incorrect. I tried 😏 Good luck.

​@darylbowman I did try that, and I don’t think it worked; still when I filter between two times it gives me no records. But I know there are records between that time before I filter it.

Here is what I put in the GI:

Thanks anyways!

It was another clever idea from Daryl. I think your checkbox idea will work. You can try this for the formula and then use any checkbox field (ex. InventoryItem.IsStk) as the schema field.

=IIF(CDEC(HOUR([INSiteStatus.LastModifiedDateTime]))+CDEC(MINUTE([INSiteStatus.LastModifiedDateTime])/60.000000) >= 1.5 and CDEC(HOUR([INSiteStatus.LastModifiedDateTime]))+CDEC(MINUTE([INSiteStatus.LastModifiedDateTime])/60.000000) <= 4, TRUE, FALSE)

I’m not sure if something is off here, the checkbox seems to be checking things at random.

If I filter the box to true, which should be from 1:30am - 4am I get a lot of PM items but there was one 1:54 am time stamp that I was able to find.

Then if I filter false, I see a lot of AM and a couple that should have been checked but weren’t

Here is the expression I ended up with: 

=IIF(CDEC(HOUR([AMBatch.CreatedDateTime]))+CDEC(MINUTE([AMBatch.CreatedDateTime])/60.000000) >= 1.5 and CDEC(HOUR([AMBatch.CreatedDateTime]))+CDEC(MINUTE([AMBatch.CreatedDateTime])/60.000000) <= 4, TRUE, FALSE)

bodiec wrote:

I’m not sure if something is off here, the checkbox seems to be checking things at random.

If I filter the box to true, which should be from 1:30am - 4am I get a lot of PM items but there was one 1:54 am time stamp that I was able to find.

Then if I filter false, I see a lot of AM and a couple that should have been checked but weren’t

Here is the expression I ended up with: 

=IIF(CDEC(HOUR([AMBatch.CreatedDateTime]))+CDEC(MINUTE([AMBatch.CreatedDateTime])/60.000000) >= 1.5 and CDEC(HOUR([AMBatch.CreatedDateTime]))+CDEC(MINUTE([AMBatch.CreatedDateTime])/60.000000) <= 4, TRUE, FALSE)

I had a thought that it is a time Zone issue, but I don’t think that is happening because I have a lot of times that are right next to each other that some are checked and others aren’t

I just tested on a site and it looked OK. Do you have grouping in the GI?

Ah, you know what, I do.

It was there from before, someone else had made this GI,

Is there any way around it? Or do I have to take off the grouping?

We had a guy create this GI while he was implementing Acumatica into our Business and we wanted to see a Sum of the Production Move Transactions, so that if we made a mistake on a quantity, it would show us the sum or total instead of showing each transaction separate.

In our data field we have this =[AMMTranSplit.Qty]*[AMMTran.InvtMult]

I’ve never known if that was what gave us the sum of the transaction or if it is the grouping that does that.

Actually, I did deactivate the grouping and still found the same issue, the checked boxes are not within the time zone. 

I will do some more testing for now, but if there is anything else you can think of please let me know

Kind of an unfortunate issue - Nothing is ever easy. 

I would be happy to take a look at the GI if you want export it out an attach it here. I have a feeling the problem is not the GI though.

Very Understandable, thank you for all the help you have done.

Here is the GI if you want to take a look, that would be great

My time calculation does not appear to be working correctly… sorry about that. I would remove from the GI. It appears to be working OK where I grabbed it from, but it’s part of a larger formula.

I modified your formula, and it appears to be good on first pass. 

=IIF(CDEC(HOUR(DateAdd( ([AMBatch.CreatedDateTime]), 'h', -6.00 )))+(CDEC(MINUTE(([AMBatch.CreatedDateTime])))/60.000000) >= 1.50 and CDEC(HOUR(DateAdd( ([AMBatch.CreatedDateTime]), 'h', -6.00 )))+(CDEC(MINUTE(([AMBatch.CreatedDateTime])))/60.000000) <= 4.00, TRUE, FALSE)

Thank you very much ​@bwhite49 this looks a lot better, 

I do see some times checked that are between 12:30 am and 1:30 am.

I’d assume that this is due to the daylight savings that you mentioned earlier. 

You said there is a more complicated way to factor in daylight savings to the times, if you do have that handy, I would love that, but I do think this gets me closer to where I want to be.

There is a screen called Daylight Saving Settings. I would mark your company’s time zone as custom. 

You will need to bring this table into the AMBatch table through a join like this…. It looks like I used time zone, but you might be able to get away with custom = TRUE

Your formula should look something like this…  where the AMBatch date is greater than the shift to date and the AMBatch date is less than the from date

From there, you just need to adjust our formula by 1 hour, so -5 instead of -6.

@Bodiec:

Use =Format([YourDateTimeField], 'HH:mm:ss') in the GI Results Grid to display only the time without the date.

Hope this helps.

​@Ankita Tayana  - The ‘Format’ function is not available to GIs; only Reports. It is a huge limitation.