Use the SourceTableTemporary property.

Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
edited 2012-11-09 in NAV Tips & Tricks
The SourceTableTemporary is a new property introduced in 5.0 giving all kinds of new capabilities of showing data that is not even in the database.

Instead of showing real data from the database, the form is running on an empty table when starting up, so you will have to populate it with data.

Step 1.

Create a normal form with the wizard and define your sourcetable. Select the columns you want to (ab)use.

Make sure to select the SourceTableTemporary property.

In our example we will use table 18, Customer and select columns No. and Name.

Step 2.

Create a new function "InitTempTable"

In this function define a Local variable "Cust", type Record, Subtype 18. (Customer).

Write the following piece of C/AL Code
Cust.SETFILTER(Balance, '>1000');
Cust.SETFILTER("Country/Region Code" , 'NL');
If Cust.FINDSET THEN REPEAT
  Rec := Cust;
  INSERT;
UNTIL Cust.NEXT = 0;

Cust.SETFILTER(Balance, '>500');
Cust.SETFILTER("Country/Region Code" , '<>NL');
If Cust.FINDSET THEN REPEAT
  Rec := Cust;
  INSERT;
UNTIL Cust.NEXT = 0;

Step 3

Go to the OnOpenForm trigger and put the new function in there.

Result

A view of your customers with a filter that is normaly not possible with the normal filters still available.

The idea and example are from form 634 Chart of Accounts Overview in 5.0. You'll also find a great example there of how to expand and collapse.

For a customer I've built a view combining 2 tables based on a date filter with quantities summed up from other tables. It is very easy to use and setup and when using proper indexing the performance is perfect.

Good luck.

Comments

  • kirankumarkirankumar Member Posts: 29
    Nice meeting you.

    This is Kiran. I have verified in my database with hotfix nav5.00 the form property you specified 'sourcetabletemporary' . But, I did't get that in my database version 4.00 SP3.

    Let please know to me about the Property with any .fob file.

    Kiran.
    Hi
  • Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    As Mark already explained, this property is only available starting from Dynamics NAV 5.0.
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    You can run similar funcionality in 4.x but you have to:

    1. Create variable based on record of your interest
    2. Set the TEMPORARY property of variable to YES
    3. Fill temporary variable with data
    4. Run the form using syntax:
    FORM.RUN(FORM::"YourFormHere",YourTempRecVariable);
    

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • todrotodro Member Posts: 117
    You can run similar funcionality in 4.x but you have to:

    1. Create variable based on record of your interest
    2. Set the TEMPORARY property of variable to YES
    3. Fill temporary variable with data
    4. Run the form using syntax:
    FORM.RUN(FORM::"YourFormHere",YourTempRecVariable);
    

    Regards,
    Slawek
    You are right, to be found in codeunit 1 function SetGlobalLanguage
    Torsten
    MCP+I, MCSE NT, Navision MCT (2004,2005)
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    Some additional info:

    Of course if you don't want to hardcode the filters you'll need to DELETEALL and re-populate the temp table. But the problem is that some genius might change SourceTableTemporary to NO and boom, your table is nuked, hope you have a backup from yesterday. So IMHO you should never DELETALL a temporary table. In the usual cases you can solve it by making it local to a function but not in this case. So what to do? My solution is to use a processing-only report where the user can set the filters the usual way, then the report will populate the temp table and run the form on it. No further processing on the form therefore no need to DELETEALL.
  • remco_rauschremco_rausch Member Posts: 68
    Deleting Records:

    So I was using this property for the first time today and found out a bit of an issue with deleting.

    When you delete a temporary record it runs all the code on the delete trigger of the record. So in my case the form was bases on the item table, when I deleted the temporary record it didn't delete the actual item record but it did delete any related data. So in my test case it deleted all the comments associated with the item.

    To stop this I put the following code on the form:
    Form - OnDeleteRecord() : Boolean
    DELETE(FALSE);
    EXIT(FALSE);

    I think that's ok, if someone has a better solution please let me know :-)
  • BeliasBelias Member Posts: 2,998
    be aware that the same does happen for other triggers,too (validates, insert, modify...)
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • BeliasBelias Member Posts: 2,998
    Are always empty can only temporarily (ie in the working memory of the client) to use when booking, and property "Temporary" = Yes must be specified but in Recordvariable anyway.
    sorry, i didn't get you :-k ...is it a question?if so, can you explain it in detail, please?
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • BeliasBelias Member Posts: 2,998
    Some additional info:

    Of course if you don't want to hardcode the filters you'll need to DELETEALL and re-populate the temp table. But the problem is that some genius might change SourceTableTemporary to NO and boom, your table is nuked, hope you have a backup from yesterday. So IMHO you should never DELETALL a temporary table. In the usual cases you can solve it by making it local to a function but not in this case. So what to do? My solution is to use a processing-only report where the user can set the filters the usual way, then the report will populate the temp table and run the form on it. No further processing on the form therefore no need to DELETEALL.
    I do it the same way, but i've got a small (probably big) problem: i have to call some functions before running the form (page in my case, but it's not important), thus i have to declare the form as a variable in order to call the functions and run the form with the same instance...i still can't find a proper function to set a temptable variable as the form variable sourcetable:
    settableview --> useless, it just sets the filters
    setrecord --> useless, it just sets the "focus"
    any idea?am i missing some parameter, maybe?
    Thanks in advance
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • BeliasBelias Member Posts: 2,998
    F**k off!!(sorry, but i'm really disappointed)i guess i can't :evil: :evil:
    http://www.mibuso.com/forum/viewtopic.php?f=23&t=38328
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • krikikriki Member, Moderator Posts: 9,094
    Little trick to send parameters to a form even if you use FORM.RUNMODAL or FORM.RUN.

    -create a singleinstance codeunit with 2 functions: "SaveInfo" and "GetInfo".

    Before running FORM.RUNMODAL, run the function "SaveInfo" in which you pass your parameter you want to give to the form
    In the OnOpenForm of your form, run function GetInfo that returns the parameter you just saved in the SingleInstance codeunit.

    I use this technique VERY often.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • BeliasBelias Member Posts: 2,998
    OMG!!! =D>
    You're OUTSTANDING!
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • David_SingletonDavid_Singleton Member Posts: 5,479
    Belias wrote:
    sorry, i didn't get you :-k ...is it a question?if so, can you explain it in detail, please?

    He He just a busy day for spammers today.
    David Singleton
  • vijay_gvijay_g Member Posts: 884
    edited 2010-06-18
    kriki wrote:
    Little trick to send parameters to a form even if you use FORM.RUNMODAL or FORM.RUN.

    -create a singleinstance codeunit with 2 functions: "SaveInfo" and "GetInfo".

    Before running FORM.RUNMODAL, run the function "SaveInfo" in which you pass your parameter you want to give to the form
    In the OnOpenForm of your form, run function GetInfo that returns the parameter you just saved in the SingleInstance codeunit.

    I use this technique VERY often.


    if you want to send parameter to a form just take a variable type FORM and create a function in form to be run, before running form call this function and you can send parameter to form.
    in this case form also holds values given by report.
  • BeliasBelias Member Posts: 2,998
    i know, but form variables cannot be used for my specific problem: you probably miss my previous posts, this:
    Belias wrote:
    [...]I do it the same way, but i've got a small (probably big) problem: i have to call some functions before running the form (page in my case, but it's not important), thus i have to declare the form as a variable in order to call the functions and run the form with the same instance...i still can't find a proper function to set a temptable variable as the form variable sourcetable:
    settableview --> useless, it just sets the filters
    setrecord --> useless, it just sets the "focus"
    any idea?am i missing some parameter, maybe?
    Thanks in advance
    AND this:
    Belias wrote:
    F**k off!!(sorry, but i'm really disappointed)i guess i can't
    viewtopic.php?f=23&t=38328
    as explained in the whole topic, temptables can be passed as source only with FORM.RUN(tableno,mytemptable) syntax and not with myformvariable.run
    This means that i cannot use form variables, and then i can (and do) use kriki's suggestion.
    P.S.: as expected, it works like a charm :thumbsup:, i could finally remove those system fields i put in the table just to have their value...
    i created a monster form, i'll post some code snippets about it when i'll have some times...
    it's a form + subform with both header and lines as temporary tables populated from a report. There are also some variables "as fields" in the subform. :mrgreen:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • stevedivimaststevedivimast Member Posts: 39
    I use a different technique to solve Belias problem.

    Add a function to form (page) (the form (page) that use temporary table) like this:

    GetTempTable(VAR : Record TableName)
    TableName.COPY(Rec.TRUE);

    Tablename is the same of Rec of the Form/Page.
    Function parameter is passed by VAR and the record is of the same type of form (page) Rec.
    Notice parameters is NOT defined as temporary. Notice also i use paremater TRUE in COPY function (this is a very useful option in COPY functions!)

    I define a form (page) variable in a codeunit or in another form (page)
    Name DataType Subtype Length
    FormwithTemporaryrec Form Item Journal

    From the form/page/codeunit a call the function
    FormwithTemporaryrec.GetTempTable(X)

    X ia a variable of type rec, subtype the sabe of Rec of FormwithTemporaryrec
    X is defined as Temporary

    After this call you cal use X variable (you can insert records).
    If you open the form with
    FormwithTemporaryrec.RUNMODAL,
    the form will show the record you inserted in calling form/page/codeunit.
  • BeliasBelias Member Posts: 2,998
    Great! this is even better than the singleinstance codeunit! =D>
    I've always underestimated the COPY function...thanks a lot!
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • vijay_gvijay_g Member Posts: 884
    Belias wrote:
    Great! this is even better than the singleinstance codeunit! =D>
    I've always underestimated the COPY function...thanks a lot!

    with respect,
    As my above post when i am sending parameter to form by function then why can't we send Rec type parameter even if we have to set it Call by Var.
  • BeliasBelias Member Posts: 2,998
    vijay_g wrote:
    Belias wrote:
    Great! this is even better than the singleinstance codeunit! =D>
    I've always underestimated the COPY function...thanks a lot!

    with respect,
    As my above post when i am sending parameter to form by function then why can't we send Rec type parameter even if we have to set it Call by Var.
    Can you write the exact code you would have written, please? probably it's just mine misunderstanding of your post :-k
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • vijay_gvijay_g Member Posts: 884
    before....
    FormwithTemporaryrec.GetTempTable(Par1,par2)
    now instead of this....
    FormwithTemporaryrec.GetTempTable(Rec);
    
    where Rec is call by var in function
  • krikikriki Member, Moderator Posts: 9,094
    I
    TableName.COPY(Rec.TRUE);

    Tablename is the same of Rec of the Form/Page.
    Function parameter is passed by VAR and the record is of the same type of form (page) Rec.
    Notice parameters is NOT defined as temporary. Notice also i use paremater TRUE in COPY function (this is a very useful option in COPY functions!)

    WOW! I just learned something new! I never noticed the second parameter in the copy-functions!

    I just checked a moment and the second parameter exists only form NAV2009! It doesn't exist in 5.0SP1!
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • vijay_gvijay_g Member Posts: 884
    Notice parameters is NOT defined as temporary. Notice also i use paremater TRUE in COPY function (this is a very useful option in COPY functions!)

    comment....
    If ShareTable is true, both Record and FromRecord must be temporary; otherwise an error will occur.
  • BeliasBelias Member Posts: 2,998
    Here you are a sample on how to use the sharetable parameter as suggested by stevedivimast =D>
    (copy and paste the code in a text object: pages 50003 & 50004)
    OBJECT Page 50003 factboxtest
    {
      OBJECT-PROPERTIES
      {
        Date=23/06/10;
        Time=12.50.23;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        SourceTable=Table27;
        PageType=List;
        SourceTableTemporary=Yes;
      }
      CONTROLS
      {
        { 1101366000;0;Container;
                    ContainerType=ContentArea }
    
        { 1101366001;1;Group  ;
                    Name=Group;
                    GroupType=Repeater }
    
        { 1101366002;2;Field  ;
                    SourceExpr="No." }
    
        { 1101366003;2;Field  ;
                    SourceExpr=Description }
    
      }
      CODE
      {
    
        PROCEDURE FNTPopTable@1101366000(VAR Tbsource@1101366000 : Record 27);
        BEGIN
          Tbsource.COPY(Rec,TRUE);
        END;
    
        BEGIN
        END.
      }
    }
    
    OBJECT Page 50004 cardpagetest
    {
      OBJECT-PROPERTIES
      {
        Date=23/06/10;
        Time=12.50.56;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        SourceTable=Table27;
        PageType=Card;
        OnOpenPage=BEGIN
                     CurrPage.factboxtest.FORM.FNTPopTable(TBTempitem);
                     TBTempitem."No." := 'mirkomirkom';
                     TBTempitem.INSERT;
                   END;
    
        OnAfterGetRecord=BEGIN
                           TBTempitem."No." += 'm';
                           TBTempitem.INSERT;
                         END;
    
      }
      CONTROLS
      {
        { 1101366000;0;Container;
                    ContainerType=ContentArea }
    
        { 1101366001;1;Group  ;
                    Name=General;
                    GroupType=Group }
    
        { 1101366002;2;Field  ;
                    SourceExpr="No." }
    
        { 1101366003;2;Field  ;
                    SourceExpr="No. 2" }
    
        { 1101366004;2;Field  ;
                    SourceExpr=Description }
    
        { 1101366005;2;Field  ;
                    SourceExpr="Search Description" }
    
        { 1101366006;0;Container;
                    ContainerType=FactBoxArea }
    
        { 1101366007;1;Part   ;
                    Name=factboxtest;
                    PagePartID=Page50003 }
    
      }
      CODE
      {
        VAR
          TBTempitem@1101366000 : TEMPORARY Record 27;
    
        BEGIN
        END.
      }
    }
    
    
    this example is really stupid, but interesting because it shows how powerful is this function if you want to determine the behaviour of factboxes, too.

    Remark: i noticed a small drawback of the sharetable method VS the singleinstance method: if you have a page that has NOT the sourcetabletemporary property set to yes, in order to use the sharetable method you have to replicate the same page, and set the sourcetabletemporary to yes. Otherwise the copy function will return an error, as expected.
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • stevedivimaststevedivimast Member Posts: 39
    t's also possible in a Page with a temporary Rec to change "dinamically" the Rec Itself.

    To do this add a function to this page like this:

    ChangeTmpRec(VAR TableName : TEMPORARY Record Item)
    Rec.COPY(TableName,TRUE);
    EXIT;

    Please, notice the sharetable parameter in COPY function.
    Then if you use a Page Variable (in a page or in a codeunit) you can do this:

    PageVar.ChangeTmpRec(TempItemVar)
    PageVar.RUN (or RUNMODAL)

    (TempItemVar is defined as Record Item Temporary)

    The page will use TempItemVar instead of original Page Rec.
    Tested in Nav2009 and NAv2009Sp1)

    This technique is similar to that one on my previous post.
    In this case i "tell" to tha page the temporary record to use, in the previuos one I "ask" to the page the teporary recor it uses.

    If you try to use this technique with a form you get a very strange behavior:
    The first time you open the form it work fine. The second one will crash Navision (you have to restart).
    I sended this error to Miscosoft people.
  • Ravi_ThakkarRavi_Thakkar Member Posts: 392
    You can run similar funcionality in 4.x but you have to:

    1. Create variable based on record of your interest
    2. Set the TEMPORARY property of variable to YES
    3. Fill temporary variable with data
    4. Run the form using syntax:
    FORM.RUN(FORM::"YourFormHere",YourTempRecVariable);
    

    Regards,
    Slawek
    To have the same functionality for RTC PAge, then what can be done?
    For your information, replacing just FORM to PAGE is not working in above code.

    Please, suggest.
    Ravi_Thakkar
    Ahmedabad, Gujarat, India
    E Mail : ravi.thakkar@hotmail.com
  • tburgertburger Member Posts: 6
    I am trying to do this with a ListPart Page in NAV 2009 R2 but nothing seems to work. I want a FactBox with a calulated number of lines based on the line that is being filtered. Think about this example as if I was pulling the top 5 customers around the customer that is chosen.
  • TimSimmondsTimSimmonds Member Posts: 47
    tburger wrote:
    I am trying to do this with a ListPart Page in NAV 2009 R2 but nothing seems to work. I want a FactBox with a calulated number of lines based on the line that is being filtered. Think about this example as if I was pulling the top 5 customers around the customer that is chosen.

    An old post... but did you have any sucess with this? We want to do a similar thing too.

    cheers, Tim
  • BeliasBelias Member Posts: 2,998
    some builds in the NAV R2 fires the onaftergetrecord trigger unexpectedly.
    e.g.: you open the page and the client fires the onaftergetrecord trigger like an heavy machine gun (ref. metal slug :mrgreen: ), then the trigger is not fired anymore when you browse through records.
    Try with the last build.
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • TimSimmondsTimSimmonds Member Posts: 47
    Belias wrote:
    some builds in the NAV R2 fires the onaftergetrecord trigger unexpectedly.
    e.g.: you open the page and the client fires the onaftergetrecord trigger like an heavy machine gun (ref. metal slug :mrgreen: ), then the trigger is not fired anymore when you browse through records.
    Try with the last build.

    Thanks for the heads up on that one. I've seen it doing strange things already. I don't think it will solve my requirement of having a factbox update against a list part line though as we can't know what the current record is. We could do with the "OnAfterGetCurrRecord" trigger back. [-o<
  • awarnawarn Member Posts: 261
    TimSimmonds,

    I had this same issue, and managed to solve it by making the list page a PageType Worksheet, and using the OnAfterGetRecord trigger to call a function in the factbox, to which I was passing information. The factBox was a cardpart.

    I tried a lot of updating functions, and none of them seemed to work but I stumbled upon that as a solution.

    This was on 2009 build 32012.
Sign In or Register to comment.