A BLOG ENTRY WRITTEN FROM FRUSTRATION!!
I’ve been banging my head against the wall all afternoon, trying to do “something simple” in Microsoft Business Central (v16). I’ve some use-cases from my business about creating a role center homepage which is full of some 18 Cues. They can be used as a form of workflow to let you highlight potential problems with the data. Luckily the business users along with a BA documented every Cue, with the logic they want in the query.
Cues are the nice square boxes with values in which make up a nice dashboard affair – the sort of thing managers love! The value in each box is really the results of any query you can run that you would normally have to ‘group by’ if you were writing the SQL. So you can sum, average, min, max, count, and do an exists using them, then display the result in the box (the normal Cues max at 999+ though, so beware!). As long as you tell the code what the list page is for the Cue, it automatically takes the user to the list page with the data filter to match the query for you – pretty impressive and powerful.
So I had a mixture of requirements for the 18 queues. Many were based around… “If flag A is not set to true, and the date field X has passed”. So you’ve not done something to the data in the table by a certain date being passed. You’re late!
Sounds very very simple… yet took me hours to work out in the code using AL language and flow field/flow filter fields in a base table.
The problem is I don’t always understand filters and their syntax – especially as the syntax changes between filters on pages and filters in flowfields! What doesn’t help is the distinct lack of documented EXAMPLES in Microsoft’s documentation. I find their BC documentation is minimal at best, and it isn’t helped by the fact they race through versions (a major upgrade every six months) and don’t seem to catch-up with the documentation on their website.
The problem is, all the examples I can find show me comparisons of dates within the where clause of a CalcFormula as = [equals] comparisons. I wanted everything BEFORE a date, not ON the date.
‘Help’ I could find said I should create an extra FlowFilter field on the table, set as a Date field, and set it when opening the screen the Cues will be within. I added that field, which can then be used within the FlowField CalcForumla command, to compare against a date field.
Now the secret is which I didn’t get to begin with – is that you don’t JUST pass a value to that FlowFilter field (a date in this case), you set up a FILTER and a value and pass that to the field. Then you can just use the = comparator… the filter comparison does NOT happen in the CalcFormula where clause at all.
OK so these 3 code snippets may help. I’m trying to select a count of all rows in a table “IC Items M04”, where the field “On sale Date” is BEFORE today’s date. That could be any date, but today is correct for the workflow.
Firstly, in the table object which holds the Cue data, add a FlowFilter field to hold the filter we will use in the flowField field.
field(100; "Current Date Filter"; Date)
{
/*
this field is used to filter down other flowfields - needs to be set-up in the screen calling to be the current date
*/
Editable = false;
FieldClass = FlowFilter;
}In the PagePart where the Cues are contained, add a OnOpenPage() trigger, and set the filter up on the “Current Date Filter” to be ‘less than today’
trigger OnOpenPage()
begin
// if no row in the cue table, create and save one! Only ever one row in a table holding cue data!
Reset();
If not Get() then begin
Init();
Insert();
end;
SetFilter("Current Date Filter", '< %1', WorkDate()); // workdate is todays date in our system
end;Then back in the table object where you are holding the Cue fields, use that Flow Filter field in your comparisons. The code here looks like it will just get rows where the “on sale date” EQUALS a field – but no, the field holds the value AND the filter of <%1 – so it gets back everything on sale before this date, not equal to it.
field(4; "Onsale Changed Onsale Past"; Integer)
{
Caption = 'Onsale Changed Onsale Past';
// “On Sale Date Changed” Flag set & “On Sale Date Change” field is in the past
Editable = false;
FieldClass = FlowField;
CalcFormula = count("IC Items M04" where("On Sale Date Changed" = const(true), "On Sale Date" = field("Current Date Filter")));
}That’s about it – once I’ve got the code all running I’ll show the screen with data populated!
