save data in new table

Hi,

I made a new table in nav 2009: 50012.

And I use a form where some tables are combined. The form looks like this:

The fields startdate and begindate has to be stored in the new table(50012).

The propertie omschrijving belongs to table 90. So what I did was in the designer of table 90 add this: design of table and then go to properties of table 90 and then added this:

And in the table 50012 I have added this:



OnInsert()
  IF "No." = '' THEN BEGIN
        BeginDate := xRec.BeginDate;
  MESSAGE('hallo');
END;

OnModify()

OnDelete()

OnRename()

test - OnValidate()

test - OnLookup()

BeginDate - OnValidate()

BeginDate - OnLookup()

EndDate - OnValidate()

EndDate - OnLookup()

Subscription - OnValidate()

Subscription - OnLookup()

No. - OnValidate()
 BeginDate := xRec.BeginDate;
 MESSAGE('hallo');

No. - OnLookup()

But nothing has been saved in the database.

Thank you

Hi Niels,

Not exactly sure what you are trying to do?

  1. You display a form (item list?) with a begin and end date.
  2. You created a new table where you want to save the begin and end date.
  3. Then you change in the relation properties of the “number” field in table 90!

And then you don’t understand where the data is? In which step did you add data?
And you didn’t do any code to support this.

But I really still have no idea, exactly what you are trying to achieve.
It looks like you are trying to create an “attribute” table to the item table, but not quite sure.
Can you explain?

Also when you ask questions, then please start by adding a bit more “context” to your description. Like in this case, then I don’t really think what you do (if I understood it), is the correct path. Understanding the “context” of the requirement, makes it easier to reply to you. But on the other hand, if the question is too long, or have too much text, then they may not even read it all.

I have created a new table and gave it number: 50012. This table I created because I want to have on FORM 36 some new properties like startdate and enddate. But FORM 36 use the table 90. But I can’ t Add properties to table 90. Because I don’t have license to edit that table. So I have created a new table 50012. And now I add the properties startdate and enddate in the from 36. But I want to link table 50012 to table 90 and that the new properties startdate and enddate in from 36 are been saved in the new table 50012. I hope it will be now more clear what I want to archieve. Thank you

For cleareness this is the structure of table 90:


Documentation()

OnInsert()
Item.GET("Parent Item No.");

OnModify()

OnDelete()

OnRename()

Parent Item No. - OnValidate()

Parent Item No. - OnLookup()

Line No. - OnValidate()

Line No. - OnLookup()

Type - OnValidate()
"No." := '';
"Variant Code" := '';

Type - OnLookup()

No. - OnValidate()
TESTFIELD(Type);
"Variant Code" := '';
IF "No." = '' THEN
  EXIT;

CASE Type OF
  Type::Item:
    BEGIN
      Item.GET("No.");
      Item.CALCFIELDS("Bill of Materials");
      "Bill of Materials" := Item."Bill of Materials";
      Description := Item.Description;
      "Unit of Measure Code" := Item."Base Unit of Measure";
    END;
  Type::Resource:
    BEGIN
      Res.GET("No.");
      "Bill of Materials" := FALSE;
      Description := Res.Name;
      "Unit of Measure Code" := Res."Base Unit of Measure";
    END;
END;

No. - OnLookup()

Bill of Materials - OnValidate()

Bill of Materials - OnLookup()

Description - OnValidate()

Description - OnLookup()

Unit of Measure Code - OnValidate()

Unit of Measure Code - OnLookup()

Quantity per - OnValidate()

Quantity per - OnLookup()

Position - OnValidate()

Position - OnLookup()

Position 2 - OnValidate()

Position 2 - OnLookup()

Position 3 - OnValidate()

Position 3 - OnLookup()

Machine No. - OnValidate()

Machine No. - OnLookup()

Production Lead Time - OnValidate()

Production Lead Time - OnLookup()

BOM Description - OnValidate()

BOM Description - OnLookup()

Variant Code - OnValidate()
IF "Variant Code" = '' THEN
  EXIT;
TESTFIELD(Type,Type::Item);
TESTFIELD("No.");
ItemVariant.GET("No.","Variant Code");
Description := ItemVariant.Description;

Variant Code - OnLookup()

Installed in Line No. - OnValidate()
IF "Installed in Line No." <> 0 THEN BEGIN
  IF "Installed in Line No." = "Line No." THEN
    ERROR(Text000,FIELDCAPTION("Installed in Line No."));
  BOMComp.RESET;
  BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
  BOMComp.SETRANGE(Type,BOMComp.Type::Item);
  BOMComp.SETRANGE("Line No.","Installed in Line No.");
  BOMComp.FIND('-');
  BOMComp.TESTFIELD("Quantity per",1);
  "Installed in Item No." := BOMComp."No.";
END ELSE
  "Installed in Item No." := '';

Installed in Line No. - OnLookup()
BOMComp.RESET;
BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
BOMComp.SETRANGE(Type,BOMComp.Type::Item);
BOMComp.SETFILTER("Line No.",'<>%1',"Line No.");
CLEAR(BillOfMaterials);
BillOfMaterials.SETTABLEVIEW(BOMComp);
BillOfMaterials.EDITABLE(FALSE);
BillOfMaterials.LOOKUPMODE(TRUE);
IF BillOfMaterials.RUNMODAL = ACTION::LookupOK THEN BEGIN
  BillOfMaterials.GETRECORD(BOMComp);
  VALIDATE("Installed in Line No.",BOMComp."Line No.");
END;

Installed in Item No. - OnValidate()
IF "Installed in Item No." <> '' THEN BEGIN
  BOMComp.RESET;
  BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
  BOMComp.SETRANGE(Type,BOMComp.Type::Item);
  BOMComp.SETRANGE("No.","Installed in Item No.");
  BOMComp.FIND('-');
END;

VALIDATE("Installed in Line No.",BOMComp."Line No.");

Installed in Item No. - OnLookup()
BOMComp.RESET;
BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
BOMComp.SETRANGE(Type,BOMComp.Type::Item);
BOMComp."No." := "Installed in Item No.";
BOMComp.SETFILTER("Line No.",'<>%1',"Line No.");
CLEAR(BillOfMaterials);
BillOfMaterials.SETTABLEVIEW(BOMComp);
BillOfMaterials.EDITABLE(FALSE);
BillOfMaterials.LOOKUPMODE(TRUE);
IF BillOfMaterials.RUNMODAL = ACTION::LookupOK THEN BEGIN
  BillOfMaterials.GETRECORD(BOMComp);
  VALIDATE("Installed in Line No.",BOMComp."Line No.");
END;

you need to add the primary key fields of table 90 also in 50012 which builds relation between both tables.

how to do that? And How to save the data. Can you give an example. Thank you. And I have already done that - joining the tables . See my image.

how did you add fields startdate and enddate in table 50012?
similarly you need to add primary key fields from table 90 to 50012.

And I already linked table 90 to table 50012. see image:

So test of table 50012 is the primary key

question is: do you have primary key fields in table 50012?
where did you set above relation?

?? I have written above that test is primary key in table 50012. So you have an example?

I linked the two tables, like this:

Or how to do it else?

If you have access to see the C/AL code (that you copied to the post) of table 90, then you can also add new fields (in the 50000 to 99999 no. range). Do you have a partner or end-user development license?

Because adding the directly to the table would be 200% easier! Even if you succeed in getting the table 50012 to store the data, then your next tasks would be to retrieve the data when reading each table 90 record. Not to mention if you want to filter the BOM lines by your new fields! This is never going to be a good solution, even if you spent weeks programming it… Sorry. [:(]

If you still wanna go on with this “method” then you need to link your tables. That is not done by changing table 90 (which I assumed you didn’t permissions to change), but by adding the primary key of the table you want to link to in your new table.

The primary key of table 90 is Parent Item No. and Line No., so that’s the fields you need to add to table 50012 as its primary key. This will allow you to create two simple functions in table 36 and call them from form 36:

NEW FUNCTIONS in table 50012

GetDates(BOM,VAR StartDate,VAR EndDate)
// Called from OnAfterGetRecord in form 36
IF GET(BOM."Parent Item No.",BOM."Line No.") THEN BEGIN
  StartDate := "Start Date";
  EndDate := "End Date";
END;

SetDates(BOM,StartDate,EndDate)
// Called from OnValidate of start and end dates in form 36
"Parent Item No." := BOM."Parent Item No.";
"Line No." := BOM."Line No.";
"Start Date" := StartDate;
"End Date" := EndDate;

IF NOT MODIFY THEN 
  INSERT;


That will solve you show and modify, but not the filtering issue…

@Erik, thank you. But where I have to add this function?

Because I have it now like this:


Documentation()

OnInsert()
GetDates(BOM,VAR StartDate,VAR EndDate)
// Called from OnAfterGetRecord in form 36
IF GET(BOM."Parent Item No.",BOM."Line No.") THEN BEGIN
  StartDate := "Start Date";
  EndDate := "End Date";
END;

SetDates(BOM,StartDate,EndDate)
// Called from OnValidate of start and end dates in form 36
"Parent Item No." := BOM."Parent Item No.";
"Line No." := BOM."Line No.";
"Start Date" := StartDate;
"End Date" := EndDate;

IF NOT MODIFY THEN 
  INSERT;
 
  
   
     {
  IF "No." = '' THEN BEGIN
        BeginDate := xRec.BeginDate;
        EndDate := xRec.EndDate;
  MESSAGE('hallo');
END;
}

OnModify()

OnDelete()

OnRename()

test - OnValidate()
 BeginDate := xRec.BeginDate;
 MESSAGE('hallo2');

test - OnLookup()

BeginDate - OnValidate()

BeginDate - OnLookup()

EndDate - OnValidate()

EndDate - OnLookup()

Subscription - OnValidate()

Subscription - OnLookup()

No. - OnValidate()
 BeginDate := xRec.BeginDate;
 MESSAGE('hallo3');

No. - OnLookup()

Oke, I have it now like this:


Documentation()

OnInsert()
{
GetDates(BOM,VAR StartDate,VAR EndDate)
// Called from OnAfterGetRecord in form 36
IF GET(BOM."Parent Item No.",BOM."Line No.") THEN BEGIN
  StartDate := "Start Date";
  EndDate := "End Date";
END;

SetDates(BOM,StartDate,EndDate)
// Called from OnValidate of start and end dates in form 36
"Parent Item No." := BOM."Parent Item No.";
"Line No." := BOM."Line No.";
"Start Date" := StartDate;
"End Date" := EndDate;

IF NOT MODIFY THEN 
  INSERT;
 }
  
   
     {
  IF "No." = '' THEN BEGIN
        BeginDate := xRec.BeginDate;
        EndDate := xRec.EndDate;
  MESSAGE('hallo');
END;
}

OnModify()

OnDelete()

OnRename()

test - OnValidate()
 BeginDate := xRec.BeginDate;
 MESSAGE('hallo2');

test - OnLookup()

BeginDate - OnValidate()

BeginDate - OnLookup()

EndDate - OnValidate()

EndDate - OnLookup()

Subscription - OnValidate()

Subscription - OnLookup()

No. - OnValidate()
 BeginDate := xRec.BeginDate;
 MESSAGE('hallo3');

No. - OnLookup()

GetDates(BOM : Record "BOM Component";VAR StartDate : Date;VAR EndDate : Date)
// Called from OnAfterGetRecord in form 36
IF GET(BOM."Parent Item No.",BOM."Line No.") THEN BEGIN
  StartDate := "BeginDate";
  EndDate := "EndDate";
END;

SetDates(BOM : Record "BOM Component";VAR StartDate : Date;VAR EndDate : Date)
// Called from OnValidate of start and end dates in form 36
BOM."Parent Item No." := BOM."Parent Item No.";
"Line No." := BOM."Line No.";
"Start Date" := StartDate;
"End Date" := EndDate;

IF NOT MODIFY THEN 
  INSERT;

But I get an error on this line:

“Line No.” := BOM.“Line No.”;

unknow variable: “Line No.”

Oke, I have it now lik this:


Documentation()

OnInsert()
{
GetDates(BOM,VAR StartDate,VAR EndDate)
// Called from OnAfterGetRecord in form 36
IF GET(BOM."Parent Item No.",BOM."Line No.") THEN BEGIN
  StartDate := "Start Date";
  EndDate := "End Date";
END;

SetDates(BOM,StartDate,EndDate)
// Called from OnValidate of start and end dates in form 36
"Parent Item No." := BOM."Parent Item No.";
"Line No." := BOM."Line No.";
"Start Date" := StartDate;
"End Date" := EndDate;

IF NOT MODIFY THEN 
  INSERT;
 }
  
   
     {
  IF "No." = '' THEN BEGIN
        BeginDate := xRec.BeginDate;
        EndDate := xRec.EndDate;
  MESSAGE('hallo');
END;
}

OnModify()

OnDelete()

OnRename()

test - OnValidate()
 BeginDate := xRec.BeginDate;
 MESSAGE('hallo2');

test - OnLookup()

BeginDate - OnValidate()

BeginDate - OnLookup()

EndDate - OnValidate()

EndDate - OnLookup()

Subscription - OnValidate()

Subscription - OnLookup()

No. - OnValidate()
 BeginDate := xRec.BeginDate;
 MESSAGE('hallo3');

No. - OnLookup()

GetDates(BOM : Record "BOM Component";VAR StartDate : Date;VAR EndDate : Date)
// Called from OnAfterGetRecord in form 36
IF GET(BOM."Parent Item No.",BOM."Line No.") THEN BEGIN
  StartDate := "BeginDate";
  EndDate := "EndDate";
END;

SetDates(BOM : Record "BOM Component";VAR StartDate : Date;VAR EndDate : Date)
// Called from OnValidate of start and end dates in form 36
Stuklijst."Parent Item No." := BOM."Parent Item No.";
Stuklijst."Line No." := BOM."Line No.";
"StartDate" := StartDate;
"EndDate" := EndDate;

IF NOT MODIFY THEN 
  INSERT;

and I have defined in globals a new recod Stuklijst that has table 90

Oke, and I add the two new functions in table 90 like this:


Documentation()

OnInsert()
Item.GET("Parent Item No.");

OnModify()

OnDelete()

OnRename()

Parent Item No. - OnValidate()

Parent Item No. - OnLookup()

Line No. - OnValidate()

Line No. - OnLookup()

Type - OnValidate()
"No." := '';
"Variant Code" := '';

Type - OnLookup()

No. - OnValidate()

TESTFIELD(Type);
"Variant Code" := '';
IF "No." = '' THEN
  EXIT;

CASE Type OF
  Type::Item:
    BEGIN
      Item.GET("No.");
      Item.CALCFIELDS("Bill of Materials");
      "Bill of Materials" := Item."Bill of Materials";
      Description := Item.Description;
      "Unit of Measure Code" := Item."Base Unit of Measure";
    END;
  Type::Resource:
    BEGIN
      Res.GET("No.");
      "Bill of Materials" := FALSE;
      Description := Res.Name;
      "Unit of Measure Code" := Res."Base Unit of Measure";
    END;
END;

ItemAddition.GetDates(xRec,ItemAddition.BeginDate,ItemAddition.EndDate);
ItemAddition.SetDates(xRec,ItemAddition.BeginDate,ItemAddition.EndDate);

No. - OnLookup()

Bill of Materials - OnValidate()

Bill of Materials - OnLookup()

Description - OnValidate()

Description - OnLookup()

Unit of Measure Code - OnValidate()

Unit of Measure Code - OnLookup()

Quantity per - OnValidate()

Quantity per - OnLookup()

Position - OnValidate()

Position - OnLookup()

Position 2 - OnValidate()

Position 2 - OnLookup()

Position 3 - OnValidate()

Position 3 - OnLookup()

Machine No. - OnValidate()

Machine No. - OnLookup()

Production Lead Time - OnValidate()

Production Lead Time - OnLookup()

BOM Description - OnValidate()

BOM Description - OnLookup()

Variant Code - OnValidate()
IF "Variant Code" = '' THEN
  EXIT;
TESTFIELD(Type,Type::Item);
TESTFIELD("No.");
ItemVariant.GET("No.","Variant Code");
Description := ItemVariant.Description;

Variant Code - OnLookup()

Installed in Line No. - OnValidate()
IF "Installed in Line No." <> 0 THEN BEGIN
  IF "Installed in Line No." = "Line No." THEN
    ERROR(Text000,FIELDCAPTION("Installed in Line No."));
  BOMComp.RESET;
  BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
  BOMComp.SETRANGE(Type,BOMComp.Type::Item);
  BOMComp.SETRANGE("Line No.","Installed in Line No.");
  BOMComp.FIND('-');
  BOMComp.TESTFIELD("Quantity per",1);
  "Installed in Item No." := BOMComp."No.";
END ELSE
  "Installed in Item No." := '';

Installed in Line No. - OnLookup()
BOMComp.RESET;
BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
BOMComp.SETRANGE(Type,BOMComp.Type::Item);
BOMComp.SETFILTER("Line No.",'<>%1',"Line No.");
CLEAR(BillOfMaterials);
BillOfMaterials.SETTABLEVIEW(BOMComp);
BillOfMaterials.EDITABLE(FALSE);
BillOfMaterials.LOOKUPMODE(TRUE);
IF BillOfMaterials.RUNMODAL = ACTION::LookupOK THEN BEGIN
  BillOfMaterials.GETRECORD(BOMComp);
  VALIDATE("Installed in Line No.",BOMComp."Line No.");
END;

Installed in Item No. - OnValidate()
IF "Installed in Item No." <> '' THEN BEGIN
  BOMComp.RESET;
  BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
  BOMComp.SETRANGE(Type,BOMComp.Type::Item);
  BOMComp.SETRANGE("No.","Installed in Item No.");
  BOMComp.FIND('-');
END;

VALIDATE("Installed in Line No.",BOMComp."Line No.");

Installed in Item No. - OnLookup()
BOMComp.RESET;
BOMComp.SETRANGE("Parent Item No.","Parent Item No.");
BOMComp.SETRANGE(Type,BOMComp.Type::Item);
BOMComp."No." := "Installed in Item No.";
BOMComp.SETFILTER("Line No.",'<>%1',"Line No.");
CLEAR(BillOfMaterials);
BillOfMaterials.SETTABLEVIEW(BOMComp);
BillOfMaterials.EDITABLE(FALSE);
BillOfMaterials.LOOKUPMODE(TRUE);
IF BillOfMaterials.RUNMODAL = ACTION::LookupOK THEN BEGIN
  BillOfMaterials.GETRECORD(BOMComp);
  VALIDATE("Installed in Line No.",BOMComp."Line No.");
END;

But If I go to form 36 and I want to add some new items: but then I get this error:

Don’t add this to table 90! Read my reply again.

And if you can add a C/AL code in table 90, then you can also add a new field!!!

@Erik,

sorry my mistake. Ofcourse not in table 90. But in from 36, like this:


Documentation()

Form - OnInit()

Form - OnOpenForm()

Form - OnCloseForm()

Form - OnQueryCloseForm() : Boolean

Form - OnActivateForm()

Form - OnDeactivateForm()

Form - OnFindRecord(Which : Text[1024]) : Boolean

Form - OnNextRecord(Steps : Integer) : Integer

Form - OnAfterGetRecord()
ItemAddition.GetDates(xRec,ItemAddition.BeginDate,ItemAddition.EndDate);
ItemAddition.SetDates(xRec,ItemAddition.BeginDate,ItemAddition.EndDate);
  

Form - OnAfterGetCurrRecord()

Form - OnBeforePutRecord()

Form - OnNewRecord(BelowxRec : Boolean)

Form - OnInsertRecord(BelowxRec : Boolean) : Boolean

Form - OnModifyRecord() : Boolean

Form - OnDeleteRecord() : Boolean

Form - OnTimer()

Form - OnCreateHyperlink(VAR URL : Text[1024])

Form - OnHyperlink(URL : Text[1024])

SetRecFilters()

InputDateStart := ItemAddition.BeginDate;
InputDateEnd := ItemAddition.EndDate;


  IF (TODAY >=  ItemAddition.BeginDate) AND (TODAY <=  ItemAddition.EndDate) THEN BEGIN
    RecSalesHeaderQuote.FncCheckBOM('affoni');
  END;

{
IF StartingDateFilter <> '' THEN
  SETFILTER("Starting Date",ItemAddition.BeginDate, StartingDateFilter);
ELSE
  SETRANGE("Starting Date");
 }

But If I open Form 36 I get still the same error that ar two many fields were specified

If you get this error, then you didn’t change the primary key of table50012.

Also why do you call SetDates here? You need to call this function from the OnValidate trigges of your date controls in the form.