Date Function Not working in Generic Inquiry

AI was used to create this answer.
Based on your requirement to modify the FromDate and ToDate parameters in Acumatica's Generic Inquiry, 

Problem:

• FromDate: You need the start of the week (@WeekStart), but Acumatica defaults to Sunday. You want it to be Saturday instead.
• ToDate: You need the end of the week (@WeekEnd), but you also need to extend it by an additional week and subtract one day.

Solution:

Modify the Default Value in the Parameters tab as follows:

1. FromDate Calculation (Saturday instead of Sunday):

   graphql  CopyEdit

   =DateAdd("d", -1, @WeekStart)

   • This moves @WeekStart (Sunday) back by one day to make it Saturday.

2. ToDate Calculation (Next week's Saturday):

   graphql  CopyEdit

   =DateAdd("d", 6, @WeekEnd)

   • Since @WeekEnd is Saturday, adding 6 days moves it to next week's Friday.
   • If you want Saturday, use:

     graphql CopyEdit

     =DateAdd("d", 7, @WeekEnd)

Implementation Steps:

1. Navigate to Generic Inquiry and open your FS-Appointment inquiry.
2. Go to the Parameters tab.
3. In the Default Value column:
   • For FromDate, enter: =DateAdd("d", -1, @WeekStart)
   • For ToDate, enter: =DateAdd("d", 6, @WeekEnd) (or =DateAdd("d", 7, @WeekEnd) for Saturday).
4. Save and test the inquiry.

Expected Output:

• FromDate: Will be Saturday (one day before @WeekStart).
• ToDate: Will be Saturday of the next week (@WeekEnd + 1 week - 1 day).

This should work as expected in Acumatica Generic Inquiry.

​@saifalisabri I already checked it but unfortunately above formula code is not working in the Generic Inquiry..

AI was used to create this answer.
Since the DateAdd function might not work as expected in Acumatica’s Generic Inquiry (GI) default values, let's try an alternative approach.

Alternative Solution Using @WeekStart and @WeekEnd

Acumatica’s built-in functions like @WeekStart and @WeekEnd provide only the beginning and end of the current week (Sunday to Saturday). However, we need to adjust these to get the required dates.

Required Date Adjustments

1. From Date → Saturday of the current week (@WeekStart - 1 day)
2. To Date → Next Saturday (@WeekEnd + 1 week)

Correct Formula to Use in Acumatica Generic Inquiry

Since Acumatica doesn't support DateAdd() in Generic Inquiry, we can try predefined Acumatica functions:

1. For FromDate (Last Saturday instead of Sunday)

   plaintext

   CopyEdit

   =@WeekStart - 1

   • @WeekStart returns Sunday (by default).
   • Subtracting 1 day moves it back to Saturday.

2. For ToDate (Next Saturday)

   plaintext

   CopyEdit

   =@WeekEnd + 7

   • @WeekEnd returns Saturday.
   • Adding 7 days moves it to next Saturday.

Implementation Steps

1. Open Generic Inquiry for FS-Appointment.
2. Go to the Parameters tab.
3. Locate the FromDate and ToDate fields.
4. Modify the Default Value:
   • FromDate → =@WeekStart - 1
   • ToDate → =@WeekEnd + 7
5. Save and Test the inquiry.

Expected Output

If the Above Doesn't Work

If Acumatica doesn’t allow direct calculations (+ or -), try setting default values using Custom Business Logic (i.e., modify the underlying DAC field using customization).

Hi ​@nsmith51 

You should add conditions into your GI conditions, something like:

[Parameter] Is Null OR [Parameter] Is Equal [YourParameterField]

Hope this helps,

Aleks

AI was used to create this answer
Root cause: Generic inquiries in Acumatica might not always handle the Date() function in the same way as other parts of the system. The exact behavior can depend on the Acumatica version and the context of the inquiry. In some cases, the date function is not supported, or the result is not formatted as expected.

1. Use the Convert Function:

   • Instead of Date(), use the Convert function with the format string 'd'. This format string tells Acumatica to convert the DateTime value to a short date string, effectively removing the time component.

2. Example Formula:

   • If your DateTime field is named YourDateTimeField, the formula in your Generic Inquiry would look like this:

     =Convert([YourDateTimeField], 'd')
     

3. Explanation:

   • The Convert function takes two arguments: the value to convert and the format string.
   • The format string 'd' is a standard date format that represents the short date.

Additional Considerations:

• Localization: The 'd' format string will respect the date format settings of the user's locale in Acumatica. This ensures that dates are displayed according to the user's regional preferences.
• Alternative formats: If you need a different date format, you can use other format strings with the Convert function. For example, 'MM/dd/yyyy' or 'yyyy-MM-dd'.
• Acumatica Version: While this solution is generally applicable, always test it in your specific Acumatica version to ensure compatibility.
• Generic Inquiry limitations: Generic inquiries have limitations, and very complex date manipulation might be better handled by a custom GI, or a custom report.

In summary: When working with dates in Acumatica Generic Inquiries, the Convert([YourDateTimeField], 'd') approach is the most reliable way to extract and display the date portion without the time.

​@saifalisabri - Please stop posting purely AI generated answers. Repeatedly trying to get the correct answer by just using AI is not helpful to the Community. AI is available to everyone. If people are coming here, it’s to get a real response by knowledgeable humans.

​@nsmith51 - This is the correct format to get one day less than @WeekStart

darylbowman wrote:

​@nsmith51 - This is the correct format to get one day less than @WeekStart

 

I think that will actually get you the start of last week unfortunately. I’m not sure you’re able to set a default value like this unless there is a way to change what day is the week start in system settings somewhere.

Oh shoot, you’re right 🙄

You can get a specific day in relation to today with @Today-X but I don’t think that’s what you’re looking for.

You could try connecting FSAppointment to itself in a join. So FSAppointment inner join on FSAppointmentFilter (an Alias)

In the join itself you would do refnbr = refnbr and then you would add your FSappointment.ScheduledDateBegin is greater than DateAdd(‘d’, -1, @WeekStart) formula to filter out all records that do not meet this condition. I think this will work.

This would get rid of your parameters, but it sounds like you just need a list of this week's appointments?

​@aiwan  I think my requirement is not clear to you!

​@darylbowman ​@bwhite49  Thanks a lot for the responses with the details. I will check these options and will update here.