Thursday, February 22, 2024

Filter by Date Range with Business Central Web API v2.0 - OData date filter syntax

by Steve Endow

I just spent 10 minutes trying to figure out the proper syntax for date filtering with Business Central Web API v2.0 endpoints, so I am documenting it here for future reference.

I don't regularly work with BC Web APIs these days, so when I have to dig back into them every 3-6 months, I'm almost always rusty and have a hard time remembering the URL format and the OData filter syntax used by Business Central Web APIs.

Today I was trying to filter the results returned from the generalLedgerEntries endpoint.  If I try to call that endpoint without a filter, Postman gives me an error because it is returning too much data.

The body of this response was too large to be saved

Fine, let's filter based on postingDate.  But I don't remember the OData filter syntax.  Unfortunately, the OData documentation has ZERO examples of filtering by date.

When I search for OData filter examples using dates, I see samples for other applications that look like this:

$filter=postingDate ge datetime'2022-01-01T00:00:00.000' and postingDate le datetime'2022-12-31T00:00:00.000'

Let me save you some time:  This OData filter format does NOT work with Business Central Web APIs.  

Here is the format to filter by date on a Business Central Web API v2.0 endpoint:

generalLedgerEntries/?$filter=postingDate ge 2021-01-01 and postingDate le 2022-12-31


Here's what it looks like in Postman:



And here is a sample encoded URL:

https://api.businesscentral.dynamics.com/v2.0/<tenant>/Sandbox1/api/v2.0/companies(<guid>)/generalLedgerEntries/?%24filter=postingDate%20ge%202021-01-01%20and%20postingDate%20le%202022-12-31


Key points:

  1. Prefix the filter with ?
  2. Standard Odata filter operators like gt and ge do work
  3. Do not put apostrophes or quotes around the date values
  4. Do not use any date conversion functions to format the date value


Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central and related technologies.

You can also find him on Twitter and YouTube, or through these links:  links.steveendow.com 

Thursday, June 1, 2023

Business Central Simple Tip #5: Comment Lines, Standard Text Codes, and Extended Text

by Steve Endow

(Thanks to Kristen Hosman for showing me this feature!)


Business Central has a very cool feature that allows you to add text Comments to transaction lines.  I'm excited about it because I don't believe we had an equivalent feature in the Dynamics GP world.

The feature appears to be well designed and implemented in Business Central, but the official MS documentation on the elements of the features are limited, so I don't think the docs to the feature justice.

There are 3 elements to the overall "feature" in Business Central:

1. Comment Line Type

2. Standard Text Codes

3. Extended Text


Comment Lines

When entering a transaction in Business Central, such as a Sales Invoice, the Line "Type" field can be set to "Comment".  You can then manually enter comment text in the line Description field.  This is the simplest option for ad-hoc comment lines, but the Description field is limited to 100 characters, so if you need more than 100 characters, you would need to add multiple Comment lines.

Sunday, March 5, 2023

Documentation. Documentation? Documentation!

by Steve Endow

How do you create your documentation?  

When do you create your documentation?

Who in your organization creates your documentation?  Who doesn't?

Does anybody actually read your documentation?  How do you know?

Do you specifically budget for documentation in your projects?  How much?

Are your customers reluctant to pay for documentation?

Can your documentation keep up with your software releases?  Do you maintain detailed release notes?

Do you methodically and consistently update your documentation over time?  


Update:  (based on a response from Erik Hougaard on Twitter)

Do you create instructional videos or video tutorials as part of your documentation?  If so, how do you consistently record, edit, store, publish, and index such videos?  Do you have public and private (e.g. customer specific) videos?  If so, how do you handle publishing videos for those different audiences?

By now I'm sure you are excited beyond belief, as there are few topics more glamorous than "documentation".  


Is it read?

Over the last 20 years, I've worked with over 700 customers.  I've documented business processes, business procedures, ERP training guides, user manuals, customization specifications, integration designs, data mappings, installation guides, and software user manuals.

Thursday, January 19, 2023

Business Central Simple Tip #4: Exporting Leading Zeroes to Excel and then CSV- It works fine!

by Steve Endow

This may seem obvious to some, but I've learned through many painful lessons that unless I actually test a scenario and see for myself that it works exactly as desired, it can be dangerous to make assumptions about software functionality and offer promises to customers...


ERP users regularly need to export data to CSV files, and then send or use those CSV files somewhere else. 

A common problem with working with CSV files is that leading zeroes can sometimes be removed by Excel.  This typically occurs when a user opens an existing CSV file, makes a change, and then saves the CSV file.  During that CSV editing process, Excel will typically remove all leading zeroes from field values.

A customer recently asked us to create a custom file export to send to their bank.  They need a list of payments created in Business Central, as well as all of the purchasing invoices that were paid by that payment--essentially a remittance file.

To keep things simple at first, we're looking to create a custom Business Central List Page that has all of the fields the customer needs for their bank.  A user can then just click on the Open in Excel action from the list page to save the data to a file.

Open in Excel from a List Page

Tuesday, January 17, 2023

Business Central Simple Tip #3: Those Role Center "Square Things" - Cues and Action Tiles

by Steve Endow

I was recently researching those "square things" that are displayed on the Business Central Role Center "home" page.

You know, the square things:

"Square Things"

After searching my memory, I recalled they were called "Cues".  As in they provide a visual "cue" with information.

But when I started to research them and how I could create a custom Cue, found an article that mentioned both "Cues" and "Action Tiles".

What?

Thursday, December 29, 2022

Business Central Simple Tip #2: Update AppSource Apps

by Steve Endow

(There is some technical background involved in this Simple Tip, so if I got any details wrong or missed something that is important, please let me know).


Today's Business Central Simple Tip comes from the book Administrating Microsoft Dynamics 365 Business Central Online by Andrey Baludin.

It relates to "AppSource Apps" that are installed in your Business Central Online Environment.  If you have Andrey's book, turn to page 46, and look for the heading "Apps and sessions".

He provides instructions on how to navigate in the Business Central Admin Center to the "Manage Apps" page.  The Manage Apps page lets you see a list of apps that are installed in that particular Environment.

Business Central Admin Center - Manage Apps Page

Wednesday, December 28, 2022

Business Central Simple Tip #1: List Page Search

by Steve Endow

This morning I opened Jeremy Vyska's Your First 20 Hours with Business Central book to see what I could learn.

As I was scrolling through the PDF with my signature Short Attention Span (tm), glancing at the pages, I happened to notice a note.

I don't know why my pre-coffee brain happened to notice this particular note, but I saw that it used the interesting combination of words like "Search", "magic", and "more slowly".

Wait, what is this about?  What Search? 

A Note About Search Performance

So I scroll up to see what he's talking about there, and I see that it's referring to the Search function on the List page.

List Page Search Field

Filter by Date Range with Business Central Web API v2.0 - OData date filter syntax

by Steve Endow I just spent 10 minutes trying to figure out the proper syntax for date filtering with Business Central Web API v2.0 endpoint...