Hardcoding a variable

Dynamics 365 Business Central/NAV Developers Forum
#1

Hi,

I have a report which is displaying figures from a global variable of decimal datatype. I want to filter this variable to give say only value less than two. Say hardcoding the variable to give only values less than two.

How can i do this? Can someone help me Please.

Thanks

#2

You can only filter on values that are in tables. If you want to limit which entries you are looking at you can write code to check against your value.

Instead of hardcoding, I would add a text box to the Request Form with the “limiting” value. Remember to turn on the Save Values property on the Request Form so the users don’t have to enter ‘2’ each time.

#3

MonOwed:= NofMonths - NumPayments;

The above line of code is calculating MonOwed. I created a request form with MonOwed as the sourceExp. I want to put say 2 in the textbox and and it should give me only MonOwed:= 2. If i also say >6, i should be about to have all MonOwed > 6 and so on. This is the filter i want. But its not working that way for me.

Any idea please?

Thanks

#4

Hi Newcommer,

I would suggest you to loop through all the records ,or whatever you use to calculate MonOwed, and insert all the calculated MonOwed together with some identification into a temporary table.

In the requestform you should have another variable as SourceExpr, perhaps MonOwedFilter (type = text).

After calculating all MonOwed, apply the filter to the temporary table.
Then count the number of records that meet the filter into a third variable (type = integer)

Then do the actual printing in an additional datatype = integer, and do setrange 1 to counted no of records.
OnAfterGetRecord of integer put code like this…
IF Number = 1 THEN
TempRec.FINDSET(FALSE;FALSE)
ELSE
TempRec.NEXT;

That way you will loop through the temporary records, and have applied the filter that was defined by the user.

Hope it makes sense…

#5

Thank sander7. This is a good idea, but i don’t have a glue on how to program this. Can you please put me through with sample codes please?

Thanks

#6

Sure.

However i thought of maybe an even simpler solution…
Maybe you can change MonOwed to be an integer… (I would assume that all payments at either done or not, so that it is not possible to have 1.5 payments)
In that case you could maybe assign the filter from the user, to a global record-variable of type Integer, and by playing with the FIND’s you could determine if the MonOwed you have just calculated falls inside or outside the filter given by the user (Do this test in the OnPreSection-trigger of the section you want to print MonOwed, and supress printing the section if needed by use of CurrReport.SHOWOUTPUT).

But back to my original suggestion…

You already have a report, in wich you at some point calculates the MonOwed-variable.
(I assume it has a simple structure with just one dataitem, and in my example it is Customer)
In this report (or a copy of it [;)]) you need to add the following…

A new dataitem of type integer at the end of the report. (This is the one i mentioned where the actual printing will be done.)

A new global variable: TempRec - Record - Customer (REMEMBER TO MAKE IT TEMPORARY!! Properties → Temporary = yes)
(I choose the customer-table because then you will have all fields available if you need more info about the customer printed, besides the customer no. You need to choose a field that you don’t need, to store the MonOwed-value, I choose “Credit Limit”… Maybe you could get better performance of the report by choosing another table as TempRec)
A new global variable: MonOwedFilter - Text30 (This is the one you set as SorurceExpr on the control in the request form)
A new global variable: TempRecCount - Integer (This one is used for storing the number of records that fall within the filter)

In the OnAfterGetRecord-trigger of Customer-dataitem put some code like this at the end…
TempRec.INIT();
TempRec := Customer;
TempRec.“Credit Limit” := MonOwed;
TempRec.INSERT();

In OnPreDataItem-trigger of Inter-dataitem put some code like this…
TempRec.SETFILTER(“Credit Limit”,MonOwedFilter);
TempRecCount := TempRec.COUNT;
SETRANGE("Number,1,TempRecCount);

In OnAfterGetRecord-trigger of Integer-dataitem put some code like this…
IF Number = 1 THEN
TempRec.FINDSET(FALSE,FALSE)
ELSE
TempRec.NEXT;

Now all you need to do is to make the layout of the report in the sections of the Integer-dataitem, instead of in the sections of the Customer-dataitem.
(Remember to use TempRec as SourceExpr in all controls, not Customer)

#7

I have tried using your code but i am having errors on this code

TempRec.SETFILTER(Credit Limit",MonOwedFilter);

The error says “A field from a record variable was expected” and the cursor points to "Credit Limit. What is the Data type of “Credit Limit”?

Any reason why its not cpmpiling?

Thanks

#8

A " is missing.

Code should be…
TempRec.SETFILTER(“Credit Limit”,MonOwedFilter);

#9

TempRec.SETFILTER(“Credit Limit”,MonOwedFilter);

I have it written as above but it tells me "A field from a record variable was expected and the cursor goes just after “Credit Limit”.

What is the datatype of “Credit Limit” because it tells me you have specify an unknow variabel, “Credit Limit”.

But when i comment TempRec.“Credit Limit” := MonOwed; i then have the error as mentioned above.

Any idea Please?

Thanks

#10

“Credit Limit”… is your TempRec a record var to Customer table?, I say this because maybe the problem is with the name of the field… “Credit Limit (LCY)”

¿?

#11

I can not make sense of this.

NoOfMonths is an integer of a count of time periods (months).

NumPaymenrs is an Integer of a count of events (payment transactions).

MonOwed = date - events.

First you need to get the units to match. What are you trying to subtract from what?

#12

NoOfMonths is decimal and it is calculated fron the start and end date entered.

NumPayments is counting any document type called payment. right now it is counting for each payment for ea`ch customer.I am trying to make it add all the payments for each customer.

How do you think MonOwed:= NofMonths - NumPayments; is suposed be wriiten to make sense in programming?

Can you please help.

Thanks

#13

I think the confussion, is that you don’t have a clear definition of thebusiness requirement but you have already started programming.Step 1 is always to define the need from a business perspective.

Don’t try to fix the code, instead throw it out, completely forget it.

Now think in terms of the company, what are you trying to do? When two operators are differnt tyeps, you can multiply or divide them.

eg

Payments per month := NumPayments / NoOfMonths, this makes business sense.

So step one, what is it that the custoemr actually needs to see?

#14

The client wants to be able to filter by number of months customers are owing them. So i created the variable MonOwed to be the difference of the no. of months that have been queried which is NoOfMonths and the payments they make NumPayments.

They want to know the no. of months their clients are owning them

Thanks

#15

OK so now we are getting somewhere. As I said, just delete all the code you have so far, it is of no use.

So we now know the global concept. Now we need to know some key definitions.

What do you define as “no. of months their clients are owning [sic] them” I assume you mean the number of days or months since the earliest unpaid invoice? This is an important point. Lets say that in 1994 that due to rounding, one penny was not applied to a $1,000,000 invoice, meaning that they paid $999,999.99 still we need to count 14 years of unpaid invoice.

So its very important now to sit with the client and define exactly what they mean by the phrase “No of months owed”. (Also we should consider handling this as days, not months.)

#16

The customers pay every month and we want to query data range say 01/01/07 to 31/12/07 and it will be 12 and if you pay two months within this range the the subtraction will be months owed is 8 months. The client wants be be able to filter say 3months or 4 to 6 months or more the 8 months and so on.

this is what is needed.

Thanks