Auto-fill field value?

(0) Share

Report

Posted on by mysamza

358

I got a table;

table 50148 "Bank Securities"
{
    DataClassification = ToBeClassified;

    fields
    {
        field(50000; "Entry No"; Integer)
        {
            DataClassification = ToBeClassified;
        }
 
        field(50011; "Job No."; Code[20])
        {
            DataClassification = ToBeClassified;
            TableRelation = Job.No.;
        }
        field(50012; "Bill-to Customer No."; Code[20])
        {
            DataClassification = ToBeClassified;
            TableRelation = Job.Bill-to Customer No.;
        }

    }

    keys
    {
        key(PK; "Entry No", "Job No.")
        {
            Clustered = true;
        }
    }

    var
        myInt: Integer;

    trigger OnInsert()
    var
        recBankSec: Record "Bank Securities";
        EntryNo: Integer;
        recJob: Record Job;
    begin
        Clear(recBankSec);
        Clear(EntryNo);
        IF recBankSec.FindLast() then
            EntryNo := recBankSec."Entry No"   1
        else
            EntryNo := 1;
        Rec."Entry No" := EntryNo;

        recJob.SetFilter("No.", rec."Job No.");
        IF recJob.FindFirst() then begin
            Rec."Bill-to Customer No." := recJob."Bill-to Customer No.";
        end;
    end;

}

and a Card page;

page 50149 "Bank Securities"
{
    PageType = Card;
    ApplicationArea = All;
    UsageCategory = Administration;
    SourceTable = "Bank Securities";
    RefreshOnActivate = true;

    layout
    {
        area(Content)
        {
            group("Bank Security")
            {
                field("Job No."; "Job No.")
                {
                    ApplicationArea = All;
                }
                field("Bill-to Customer No."; "Bill-to Customer No.")
                {
                    ApplicationArea = All;
                    Editable = false;
            }
        }
    }

    actions
    {
        area(Processing)
        {
            /*   action(ActionName)
              {
                  ApplicationArea = All;

                  trigger OnAction()
                  begin

                  end;
              } */
        }
    }
}

On the Card page, when I select a Job No. I get the Bill-to Customer No value filled in.

However, if I then change the Job No. the Bill-to Customer No. value does not change and remains the same.

Here is an example;

Job table has a record with No. and Bill-to Customer as DME, 50000 respectively.

On my card page, on my Job No. field, I select the above mentioned record, and my Job No. field has value DME and Bill-to Customer field has value 50000. So far so good. I then change my mind to go to Job No. field to select the following record from the Job table which has a record with No. and Bill-to Customer as SME, 20000 respectively.

Now on my card page's Job No. field has value as SME however, Bill-to Customer is still 50000 and not 20000.

I have the same question (0)

All responses (7)

Answers (1)

Avinash B 1,455 on at

Like (0)

Report

Hi

You are doing a mistake by writing the code on OnInsert trigger of the table. The reason why it isn't changing when the change the Job No for the second time is that, the record would have been already inserted and hence the OnInsert trigger of the table won't execute.

You can handle this by refactoring the code. See the below code.

table 50148 "Bank Securities"

{

DataClassification = ToBeClassified;

fields

{

field(50000; "Entry No"; Integer)

{

DataClassification = ToBeClassified;

}

field(50011; "Job No."; Code[20])

{

DataClassification = ToBeClassified;

TableRelation = Job.No.;

trigger OnValidate()

var

recBankSec: Record "Bank Securities";

begin

recJob.SetRange("No.", rec."Job No.");

IF recJob.FindFirst() then

"Bill-to Customer No." := recJob."Bill-to Customer No.";

end;

end;

}

field(50012; "Bill-to Customer No."; Code[20])

{

DataClassification = ToBeClassified;

TableRelation = Customer;

}

}

keys

{

key(PK; "Entry No", "Job No.")

{

Clustered = true;

}

}

var

myInt: Integer;

trigger OnInsert()

var

recBankSec: Record "Bank Securities";

begin

Clear(recBankSec);

Clear(EntryNo);

IF recBankSec.FindLast() then

"Entry No" := recBankSec."Entry No" + 1

else

"Entry No" := 1;

end;

}

Regards

Avinash B

Was this reply helpful? Yes No
mysamza 358 on at

Like (0)

Report
Your solution gives me wrong Bill-to Customer No at selecting a Job No.

Job table has a record with No. and Bill-to Customer as 'DME', '50000' respectively.

On card page, I select the above mentioned record, and my Job No. field has value DME and Bill-to Customer field has value 40000.

Job table has a record with No. and Bill-to Customer as 'SME', '40000' respectively.

On card page, I select the above mentioned record, and my Job No. field has value SME and Bill-to Customer field has value 50000.

Card page populates wrong Bill-to Customer No.

Fixed it by changing the OnValidate to the following and it populated the Bill-to Customer on the page.

trigger OnValidate() var recJob: Record Job; begin recJob.SetRange("No.", rec."Job No."); IF recJob.FindFirst() then "Bill-to Customer No." := recJob."Bill-to Customer No."; end;

However it won't save (modify) the record and give me the following error.

Was this reply helpful? Yes No
Avinash B 1,455 on at

Like (0)

Report

Hi

I misunderstood, I thought you are copying the bill to customer from table Bank Securities.

You can still refactor the code.

IF recJob.Get(rec."Job No.") then

"Bill-to Customer No." := recJob."Bill-to Customer No.";

You can mark Yes if your query has solved.

Regards

Avinash B

Was this reply helpful? Yes No
mysamza 358 on at

Like (0)

Report

I continue to get the error I am attaching below.

It should be modifying the record?

The first time I select a Job No it is supposed to save it (drop and entry in the table), and when I go back to change a Job No it should be (renaming record?) modifying the record?

Was this reply helpful? Yes No
Suggested answer

Avinash B 1,455 on at

Like (0)

Report

Since you have defined Job No, also as PK, it will try to Rename the record.

If your idea to keep multiple entries for the same Job no. you don't need to have job no., as pk. Each time you can increase the Entry No.

Regards

Avinash B

Was this reply helpful? Yes No
mysamza 358 on at

Like (0)

Report

All I was trying to implement is what you see in the standard. If you open a 'New' Sales Order Card Page, you choose a Customer No. under General Tab -> it populates Customer Name immediately on the page. Now if user changes its mind to select a different Customer No. NAV/BC asks you if you are sure you want to change your Sell-to Customer and upon clicking 'Yes', Customer No. on page is updated and so is the Customer Name.

In my case, I want to allow user to select a Job No. and it could also then if choose to change their mind be allowed to change to another Job No. while they are still there on my Card Page. So far I have not been able to achieve this.

Was this reply helpful? Yes No
Verified answer

Avinash B 1,455 on at

Like (1)

Report
table 50548 "Bank Securities" { DataClassification = ToBeClassified; fields { field(50000; "Entry No"; Integer) { DataClassification = ToBeClassified; } field(50011; "Job No."; Code[20]) { DataClassification = ToBeClassified; TableRelation = Job; trigger OnValidate() begin CalcFields("Bill-to Customer No."); end; } field(50012; "Bill-to Customer No."; Code[20]) { FieldClass = FlowField; CalcFormula = lookup (Job."Bill-to Customer No." where("No." = field("Job No."))); TableRelation = Customer; } } keys { key(PK; "Entry No") { Clustered = true; } } var myInt: Integer; trigger OnInsert() var recBankSec: Record "Bank Securities"; begin Clear(recBankSec); IF recBankSec.FindLast() then "Entry No" := recBankSec."Entry No" 1 else "Entry No" := 1; end; }

Hi

The above code might help you.

Click yes if my suggestion solves your query.

Regards

Avinash B

Was this reply helpful? Yes No