Validate with more than one value!

Dear All,

I have a Excel sheet which I read and Import in tabel Gen. Journal Line! ofcourse while importing I validate the invoice number field. for example if I have an invoice in the Excel tabel with the number 12345 , but in my customer card the invoice number sometimes is written like this 12345/01 or 12345/02 . So each time I try to validate the invoice number i get an error though the invoice existes but with /01 or /02 at the end.

My problem is I want to say something like this :

If the (first invoice validate didnot work) // example(12345)

validate the field with the value but with /01 or 02 at the end // example (12345/01 or 12345/02)

My code:

IF NOT “Import Gen. Journal Line”.VALIDATE(“Bal. Account No.”,BalAccountNo);

Then “Import Gen. Journal Line”.VALIDATE(“Bal. Account No.”,BalAccountNo+’/01’);

Else “Import Gen. Journal Line”.VALIDATE(“Bal. Account No.”,BalAccountNo+’/02’);

Thanks alot

Hi Mohamed,

I’m assuming that you’re just using your example code to demonstrate your goal, and not that you specifically want to achieve it with the VALIDATE function? The VALIDATE function doesn’t return a value, so you can’t use the function as an argument in a conditional expression.

From your example code and your description of the issue, I’m having a difficult time of isolating your question. From your code, it looks like you’re importing into the Bal. Account No. field? Depending on the Type value, this Bal. Account No. field would typically contain either a Vendor, Customer, Bank Account No, or G/L Account No. (to name a few) value, but generally wouldn’t include an Invoice number. So, are you focusing on a value that represents the customer’s invoice number, or something else?

And, it sound like you’re wanting to inspect the value being imported (either for a preexisting value, or for compliance with an established format), and then, based on the outcome of the inspection, alter the imported value accordingly. Is that what you’re trying to do? If so, I wonder about the source data. What is it about the source data that allows you to conclude that it might be wrong and in need of being altered?

Maybe you could talk a bit more about what it is that you’re trying to do with the whole data import process?

So far as the feature you’re requesting goes, take a look at the INCSTR function?

hi,

it’s not fully clear what’s your problem. Please describe in detail (especially your target) and add screenshots of the excel doc and the import page.

Dear All,

In our company we have a special process regarding invoice numbers and corrections. Let’s say we have an invoice number with the following number 1234. Sometimes we need to adjust the invoice lines or total amount (for examples we need to delete a line from the invoice or add a new line). If this happens (Adjusting the invoice amount) we will change the invoice number from 1234 to 1234/1 ---- > this will be the new invoice number (same one but with /1 at the end).

Now let’s image that at the end of each month we get an excel sheet with all payments received from different customers. So, say that in this excel sheet there a line regarding a payment for invoice number 1234 !

So while importing this line from the excel sheet (Line contains different columns Invoice number, Payment, dates, Customer number,…) I have to validate this invoice number to make sure that this invoice exists and also to call the code written under validate.

THE ONLY PROBLEM NOW IS THAT THE INVOICE NUMBER IN OUR SYSTEM IS 1234/1 instead of 1234.So I want to import the line from the excel sheet and I want to say if Validate function did not find the invoice number with 1234 them don’t through an error and try to treated it as if invoice number 1234/1 is = 1234 (Meaning I just want to ignore the /1 at the end)

Hope this would tell all about my problem.

Thanks a lot :slight_smile: for your time,

Mohamed

Dear All,

In our company we have a special process regarding invoice numbers and corrections. Let’s say we have an invoice number with the following number 1234. Sometimes we need to adjust the invoice lines or total amount (for examples we need to delete a line from the invoice or add a new line). If this happens (Adjusting the invoice amount) we will change the invoice number from 1234 to 1234/1 ---- > this will be the new invoice number (same one but with /1 at the end).

Now let’s image that at the end of each month we get an excel sheet with all payments received from different customers. So, say that in this excel sheet there a line regarding a payment for invoice number 1234 !

So while importing this line from the excel sheet (Line contains different columns Invoice number, Payment, dates, Customer number,…) I have to validate this invoice number to make sure that this invoice exists and also to call the code written under validate.

THE ONLY PROBLEM NOW IS THAT THE INVOICE NUMBER IN OUR SYSTEM IS 1234/1 instead of 1234.So I want to import the line from the excel sheet and I want to say if Validate function did not find the invoice number with 1234 them don’t through an error and try to treated it as if invoice number 1234/1 is = 1234 (Meaning I just want to ignore the /1 at the end)

Hope this would tell all about my problem.

Thanks a lot :slight_smile: for your time,

Mohamed

hi,

why don’t you set the invoice no. from 1234 to 1234/1 in the excel doc?

Coz the excel hast more than 300 lines, and It will take a lot of time to look in Navision and search for thoses /1 invoices and then replace them in the

Coz the Excel sheet has more than 500 lines, and in Navision we have more than 5000 customer. so it will take a lot of time to open the customer one by one to see who has the /1 invoice. so doing it in Navision side would be much easier.

Best regards,

Mohamed

hi,

that means you have a process for versioning the invoices. one solution would be to change that process. add a new field in the sales invoice page named “Version” (Type Integer) and increase this number when changing the sales invoice. this would solve the problem with the changing/renaming of the sales invoice no.

hi,

if the mentioned option above is not possible for you, you coudl the following: you import the excel data using the rapid services? if yes, you could change the import process & validation. to do that change cu 8617 Config. Validate Management, there the function EvaluateTextToFieldRefCodeText as you need.

Hi,

This is not way to do the solution as I mentioned before:

  1. Check for Invoice number

  2. If its exists then validate

  3. If it doesn’t exists the ass /1 to it

  4. then validate

Iam sure this solution must work :slight_smile:

Thanks a lot,

Mohamed

how do you import the data from the excel doc? with the nav import tool (rapid services)?

The code is as follow (Object= Report) :

hi,

why do you assign the values twice?

for example:
“Import Gen. Journal Line”.“Account No.” := “1_Nr”;
“Import Gen. Journal Line”.Validate(“Account No.”, “1_Nr”);

both does the same (assigning the value), except that the second line starts the OnValidate trigger after assigning the value.

to get the correct gen. journal line with no. 1234, 1234/1 and so on you could do following:

genJnlLine.setrange(“Journal Template Name”,JournalTemplateName);
genJnlLine.setrange(“Journal Batch Name”,JournalBatchName);
genJnlLine.setrange(“Account No.”,“1_Nr.”;
genJnlLine.setrange(“Bal. Acc. Type”,“Bal. Acc. Type”::“G/L Account”);
genJnlLine.setrange(“Bal. Account No.”,BalAccountNo);
if not genJnlLine.findfirst then begin
genJnlLine.setrange(“Bal. Account No.”); // remove the “Bal. Account No.” filter
genJnlLine.setrange(“Bal. Account No.”,BalAccountNo + ‘/1’);
genJnlLine.findfirst;
// and so on
end;

you could use the COUNT function instead of the FINDFIRST.

Where, in NAV data, are you making this change? Are you modifying the primary key value in the Posted Sales Invoice Header record? Put another way, are you making this change to the Invoice Number before you post the Sales document, or afterward? If afterward, how are you making that change, and are you ensuring that you’re also updating the related fields in the Customer Ledger and General Ledger? And is this modified value the value you’re trying to find so you can properly populate the Applies-to Doc. No. field in your payment journal? I know that’s asking a lot, but I think the information is necessary to actually answer your question.