Discussions

Expand all | Collapse all

How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

QuickBaseCoach Dev./Training04-23-2019 17:07

Murali Gangineni04-24-2019 17:52

QuickBaseCoach Dev./Training04-24-2019 17:55

QuickBaseCoach Dev./Training05-03-2019 16:55

  • 1.  How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-23-2019 17:05
     Automatically Copy records from Table1 to Table2 after applying filter on Table1. 
    Table1 and Table2 dont have any relation.


  • 2.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-23-2019 17:07
    What would you want to trigger copying records?


  • 3.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 12:12
    I want the trigger to be on click of a button. On clicking the button, delete all the records from the Table2 and copy the records from Table1 to Table2. Before copying, filter has to be applied on "Article Number" column in Table2 based on the Article number present in the record in some other table from where i am clicking the button. Copy only those filtered records.

    I know we can use API_PurgeRecords to delete all the records in Table2. But i got stuck at how to copy the filtered records from Table1 to Table2. And that filter on Table1 should be dynamic filter as Article number changes from one record to another record from where i am clicking the button. 


  • 4.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 12:46
    If this just needs to work in a single concurrent user environment, you can set up a table with a single record and have a field there to hold the Focus Article Number. Then make a relationship to the detail records based on a reference field with a formula value of 1, and then then look that up down to the detail records.

    Then, make a saved table to table import subject to the filer.

    Then you would make a URL formula button which would do the following steps.

    Write the Article number into the Focus table for record with Record ID# of 1. (As there only is one record there)

    Purge table 2

    Run the saved import.

    Land the user somewhere, probably on table 2.


  • 5.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 13:53
    Thanks for the quick response,

     I didn't understand how to achieve this thing  "Make a saved table to table import subject to filter"  and   "Run the saved import" .

    Can you please explain how to do that.


  • 6.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 14:04
    You will need to set up a table called Focus Article and create 1 record.  It will be Record ID# 1.  Create a field to hold the Focus Article.

    Make a relationship to your detail table where 1 Focus Article has many detail records.  For the reference field on the right hand side, change the field type to be formula numeric with a formula of
    1


    Then lookup the focus article down to the detail records.

    Then from any table home page or the dashboard go into import export and create an import "from another table" to populate table 2 from table 1. 

    Get that far and then post back. 


  • 7.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 15:57
    I have created the Saved Import. 
    _Now how can i run the saved import. Can i use any URL or something to run the saved import on the click of a button with URL.


  • 8.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 16:20
    Can you contact me at http://www.quickbasecoach.com/ and I will help you with the code?


  • 9.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.



  • 10.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 17:32
    OK, off like you said that you have some experience with URL formula buttons.

    We will need to do four steps
    1. Set the focus
    2. Purge table 2
    3. Run the saved import 
    4. Land the user somewhere

    We will first set up the steps as formula variables and then put them together.

    The button will reside on the Article record, so that the button knows what the focus Article is.

    var text URLONE = urlroot() & "db/" & [DBID_Focus_Article] & "?act=API_EditRecord"
    & "&rid=1"
    & "&_fid_7=" &  urlencode([Article])
    & "&apptoken=xxxxxxx"; // if you have application tokens enabled every step which edits will need this line

    var text URLTWO = urlroot() & "db/" & [_DBID_Table2] & "?a=GenPurge";

    var text URLTHREE = urlroot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=10";

    var text URLFOUR = urlroot() & "db/" & [_DBID_Table2] & "?a=q&qid=1;

    $URLONE 
    & "&rdr=" & URLEncode($URLTWO)
    & URLEncode("&rdr=" & URLEncode($URLTHREE))
    & URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))

     


  • 11.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 17:52
    Thank you very much..


  • 12.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-24-2019 17:55
    Let me know when you get it working :)


  • 13.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-26-2019 15:25
    I tried as like you mentioned. Everything is working fine except that all the records are getting copied from Table1 to Table2, instead of copying only the filtered records.
    But i want to copy only filtered records after filter applied on Article Number field in Table1, based on the Article Number present in the Focus Table.


  • 14.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-26-2019 17:06
    Did you look up the focus article down to the detail records in Table 1. 
    Did you apply a filter on the saved table copy.


  • 15.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-30-2019 13:01
    Yes, It worked after i applied filter on Saved Import for the lookup Article Number.
    Also instead of API_GenPurge, I have used API_PurgeRecords because API_GenPurge prompts user whether to delete data or not. So, I need to use API_PurgeRecords which do not require any user interaction.

    Also i changed the landing page to edit mode of a record. I have duplicated the code to import data from multiples tables to Table2

    For reference, find the below working code:

    var text URLONE = URLRoot() & "db/" & [_DBID_FOCUS_TABLE] & "?act=API_EditRecord"
    & "&rid=1"
    & "&_fid_6=" &  URLEncode([Article Number]);
    & "&apptoken=xxxxxxxx";

    var text URLTWO = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_PurgeRecords&qid=1";

    var text URLTHREE = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=43";

    var text URLFOUR = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=44";

    var text URLFIVE = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=45";

    var text URLSIX = URLRoot() & "db/" & Dbid() &  "?a=er&dfid=2&rid=" & ToText([RECORD ID#]) ;

    //& "&nexturl=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=dr&dfid=2&rid=" & ToText//([Record ID#]));

    $URLONE 
    & "&rdr=" & URLEncode($URLTWO)
    & URLEncode("&rdr=" & URLEncode($URLTHREE))
    & URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))
    & URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))
    & URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSIX)))))

    Thanks man...
    It's a big challenge for my project and you helped me at the right time :)



  • 16.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-30-2019 13:06
    Well done!  Thx for letting me know.
    About that API_GenPurge, I also thought that did require user interaction, but then when I used it in a formula URL with subsequent steps, it dd not seem to stop for user intervention, so that is why I suggested it here. In my experience that API_GenPurge runs super fast for large data sets.


  • 17.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-30-2019 16:00
    Yes, You are right that using API_GenPurge in a formula URL with subsequent steps does not stop for user intervention. But then it is not deleting the records in the Table2 after executing the Formula URL.

    I have got this new challenge. If possible please help me out.
    Using Formula URL , I have to check if after applying filter on Table2 based on Article Number is returning any records or not. If it returns atleast one record then don't import else if it is returning zero records, then import the data.  I mean first i am checking whether the article number is present in the Table2 or not. If not present, I will import data from other tables to Table2. If present, then i will not import data from other tables to Table2.


  • 18.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-30-2019 20:45
    I would suggest creating a relationship between the user focus table and the table 2.

    Then a summary field of the # of records in table 2 for the Focus Article.

    Then create a formula checkbox field on the Focus record called

    [OK to proceed with Import?]

    ie if that summary field = 0.

    Ok, so now the focus record now knows it is OK to go ahead with the import.

    Look up that field [OK to proceed with Import?] down into Table 1.

    Edit the saved table to table import to add in that filter where

    [OK to proceed with Import?] is checked.

    So what should happen is that the import will always run, just like it does now, but some of the time, if the focus Article is in table 2, then the filter

    [OK to proceed with Import?]

    Will case no records to qualify for the import and it will run, but import nothing.




  • 19.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-02-2019 12:18
    Yes, Its working fine but with below issues:

    " Creating a relationship between the user focus table and the table 2." - After creating the relationship, need to change the reference field of Related Focus table to Formula Numeric and set it's value to 1. I did this and it worked.

    Also, Actually i am importing data from multiple tables into the Table2 on the click of button. Let's say i am importing from tables X,Y and Z into Table2. So, what's happening is once data is imported from Table X into Table2, the field [OK to proceed with Import?] is going to False as the Summary field is giving value other than 0 once the data is imported from Table X. As, a result of this data is not getting imported from next tables which are Table Y and Table Z.

    Is there a way to overcome this issue ?


  • 20.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-02-2019 12:18
    Yes, Its working fine but with below issues:

    " Creating a relationship between the user focus table and the table 2." - After creating the relationship, need to change the reference field of Related Focus table to Formula Numeric and set it's value to 1. I did this and it worked.

    Also, Actually i am importing data from multiple tables into the Table2 on the click of button. Let's say i am importing from tables X,Y and Z into Table2. So, what's happening is once data is imported from Table X into Table2, the field [OK to proceed with Import?] is going to False as the Summary field is giving value other than 0 once the data is imported from Table X. As, a result of this data is not getting imported from next tables which are Table Y and Table Z.

    Is there a way to overcome this issue ?


  • 21.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-02-2019 12:37
    Wow this is getting more complicated ... perhaps you need to include a field in your import to identify if the data was sourced from X Y or Z.  Then make a summary fields to to detect by type of import.


  • 22.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-03-2019 16:54
    I got it sorted. 
    Actually i have to call the Formula URL button from the BaseTable.
    In the Focus table, i have created an additional field called "CountBeforeImport". 

    Now, I have created a relationship between Focus Table and BaseTable. Then i looked up the Summary field down to the BaseTable and using API_EditRecord saved the value of Summary Field to "CountBeforeImport" field in the Focus Table through the Formula URL Button. 

    So, after every import the count of "Summary Field" changes but the value of "CountBeforeImport" remains the same as we are updating it using only API_EditRecord.

    In the Saved Import, i applied the filter criteria using "CountBeforeImport_" field.


  • 23.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-03-2019 16:55
    Ok. Good for you!


  • 24.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-07-2019 14:14
    I got an issue in that...
    As i am looking up the Summary Field of FocusTable into the BaseTable, It is giving me the last updated value of Summary Field instead of current updated value. This is happening when i click on the Formula URL Button with the below formula: 

    var text URLONE = URLRoot() & "db/" & [_DBID_FOCUS_TABLE] & "?act=API_EditRecord"
    & "&rid=1"
    & "&_fid_6=" &  URLEncode([Article Number]);
    & "&apptoken=xxxxxxxx";

    var text URLTWO = URLRoot() & "db/" & [_DBID_FOCUSMASTER] & "?act=API_EditRecord"
    & "&rid=1"
    & "&_fid_7=" &  URLEncode([FocusMaster - Summary Field - # of Graphmasters]);

    var text URLTHREE = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=43";

    var text URLFOUR = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=44";

    var text URLFIVE = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=45";

    var text URLSIX = URLRoot() & "db/" & Dbid() &  "?a=er&dfid=2&rid=" & ToText([RECORD ID#]) ;


    $URLONE 
    & "&rdr=" & URLEncode($URLTWO)
    & URLEncode("&rdr=" & URLEncode($URLTHREE))
    & URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))
    & URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))
    & URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSIX)))))


    Is there a way to get the current updated value ??

    If not,
    Is there a way to use Javascript in Formula URL to extract the number of records in the Table using API_DoQueryCount ?


  • 25.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-07-2019 14:14
    I got an issue in that...
    As i am looking up the Summary Field of FocusTable into the BaseTable, It is giving me the last updated value of Summary Field instead of current updated value. This is happening when i click on the Formula URL Button with the below formula: 

    var text URLONE = URLRoot() & "db/" & [_DBID_FOCUS_TABLE] & "?act=API_EditRecord"
    & "&rid=1"
    & "&_fid_6=" &  URLEncode([Article Number]);
    & "&apptoken=xxxxxxxx";

    var text URLTWO = URLRoot() & "db/" & [_DBID_FOCUSMASTER] & "?act=API_EditRecord"
    & "&rid=1"
    & "&_fid_7=" &  URLEncode([FocusMaster - Summary Field - # of Graphmasters]);

    var text URLTHREE = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=43";

    var text URLFOUR = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=44";

    var text URLFIVE = URLRoot() & "db/" & [_DBID_Table2] & "?act=API_RunImport&ID=45";

    var text URLSIX = URLRoot() & "db/" & Dbid() &  "?a=er&dfid=2&rid=" & ToText([RECORD ID#]) ;


    $URLONE 
    & "&rdr=" & URLEncode($URLTWO)
    & URLEncode("&rdr=" & URLEncode($URLTHREE))
    & URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))
    & URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))
    & URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSIX)))))


    Is there a way to get the current updated value ??

    If not,
    Is there a way to use Javascript in Formula URL to extract the number of records in the Table using API_DoQueryCount ?


  • 26.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-07-2019 14:21
    I cannot help you with javascript.

    But I'm not really understanding your question.  You seem to be asking if you can obtain  a record count from after the imports?


  • 27.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-07-2019 15:02
    I want to obtain the record count before the import and store that count value in a field or in a variable.


  • 28.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 05-07-2019 15:49
    Ate you saying that this line here

    & "&_fid_7=" & URLEncode([FocusMaster - Summary Field - # of Graphmasters]);

    Has the value after the Import? That would seem impossible.


  • 29.  RE: How to automatically Copy records from Table1 to Table2 after applying filter on Table1.

    Posted 04-23-2019 18:13
    Murali, If you want to have this job run on a scheduled basis, you can create a scheduled automation. Otherwise, if you want it to run when something happens, such as a new record is added, or a record is modified or deleted and so on, create an appropriate automation. Then for actions, select "Copy Records" and specify the source table as table1 and apply the filter on table1. Then specify the target table as table2 and make sure you map the fields in table2 to the source fields in table1.

    The beauty of quickbase as told in the words of Kirk Trachy is: "If you can say a requirement in a sentence, you can do it in quickbase."

    Hope that helps.