Discussions

Expand all | Collapse all

Log all data in fields from all records in Table A to a single new record in Table B

  • 1.  Log all data in fields from all records in Table A to a single new record in Table B

    Posted 06-04-2019 19:54
    Hi all!

    I'm trying to make an audit log of sorts. I have two tables involved in this. Field Tech Schedule (Table A) and Field Schedule History (Table B). Table A only has 7 records in it and won't have any more records. Table A is a housing for Teams, ALPHA through GOLF. Each record or Team has multiple fields I would like to keep track of at the end of the day and would like that data to be filed in Table B for each day. Previously, I had it formatted where a new record for each team would be created in Table B with Table A's info, BUT I am trying to instead make it to where Table B only has one record log for each day with all of the team's data from Table A in it, so one record in Table B would have field info from all the records(teams) in Table A.
     
    So, Table B would have one record that has:
    ALPHA - Field Techs
    BETA - Field Techs
    CHARLIE - Field Techs
    .
    ..
    ...
    GOLF - Field Techs
    ALPHA - Jobs
    BETA - Jobs
    etc.....

    I was trying to accomplish this with an automation triggered off of a URL button that changed a checkbox, but when describing the action, it won't let me take data from a specific field other from the record the button was pressed. For instance, within Table A and record ALPHA, if the button was pressed, I could only record info from record ALPHA.

    Is there an URL formula that can take data from specific fields within a specific table with a specific record ID and write it to a new record in another table?
    I already have all the fields in Table B set up to receive all the info from each team in Table A.
    Follow up to that..Is there a way to put this button on the homepage dashboard?


    EXTRA INFO:

    Table A dbid: bpjmhb64x
    Table B dbid: bpnrreyt5

    Table A's RIDs:
    ALPHA - rid_1
    BETA - rid_2
    CHARLIE - rid_3
    DELTA - rid_4
    ECHO - rid_5
    FOXTROT - rid_6
    GOLF - rid_7

    Basically, what I am trying to do....
    Table A to Table B info copy:
    rid_1 fid_7 -> fid_20
    rid_2 fid_7 -> fid_21
    rid_3 fid_7 -> fid_22
    rid_4 fid_7 -> fid_23
    rid_5 fid_7 -> fid_24
    rid_6 fid_7 -> fid_25
    rid_7 fid_7 -> fid_26
    rid_1 fid_8 -> fid_49
    rid_2 fid_8 -> fid_50
    rid_3 fid_8 -> fid_51
    rid_4 fid_8 -> fid_52
    rid_5 fid_8 -> fid_53
    rid_6 fid_8 -> fid_54
    rid_7 fid_8 -> fid_55
    rid_1 fid_28 -> fid_42
    rid_2 fid_28 -> fid_43
    ect...ect..


    Please Help!






  • 2.  RE: Log all data in fields from all records in Table A to a single new record in Table B

    Posted 06-27-2019 16:50
    anyone?


  • 3.  RE: Log all data in fields from all records in Table A to a single new record in Table B

    Posted 06-27-2019 19:35
    Perhaps create a field in Table A that appends / concatenates the contents of all required fields entered per each new record on Table A.

    You could then set up a Quick Base action whereby when field x on Table A changes, and any conditions of your choosing are true, perform action +Add a Record to Golf App -> Table B.


  • 4.  RE: Log all data in fields from all records in Table A to a single new record in Table B

    Posted 06-28-2019 00:16
    It's not clear from your post if you want to create a Daily History record of all 7 Team Records or some sort of totals record.

    But regardless, the method is the same and easy.

    You set up a saved table to table copy to copy from Table A to table B.  Then set up a scheduled Automation to run Daily or maybe weekly 5 days a week from Tuesday thru Saturday. The Automation will trigger some time shortly after midnight.

    If you want to copy all 7 records, then you would just set up the Saved table to table copy to copy all 7 records.  If you just wanted to copy the totals, then you would need to create a single daily totals record in a new table, relate it to all records in the Teams table and use summary fields to get your totals.   


  • 5.  RE: Log all data in fields from all records in Table A to a single new record in Table B

    Bronze
    Contributor
    Posted 06-28-2019 00:46
    But the fields in the tables are not the same. He has 7 records in Table A, with 7 fields each, and he wants to take that data and create one snapshot record in Table B with all 49 fields represented separately.


  • 6.  RE: Log all data in fields from all records in Table A to a single new record in Table B

    Posted 06-28-2019 02:10
    OK , now I get the ask.

    "no Problem"

    Set the key field of Table B to be the Date field.

    Make a saved table to table copy to import Team A only into table B and map the fields into table A's set of fields.  Set the saved table to table import to merge on the date field.
    Run the import manually and observe that it will create a record.  it will copy 1 record and create 1 record.


    then,

    Copy the saved import and change the filter to only copy Team B only but map the fields into Team B's respective fields. It will copy just 1 record, but Merge it with the existing record for that date so no new records, will be created.  

    repeat for all 7 Teams.

    Then just set the single Automation to run Daily, but it will have 7 steps.

    Maybe set it to run every day, but filtered where some data exists to copy across, just in case they work on weekends.


     


  • 7.  RE: Log all data in fields from all records in Table A to a single new record in Table B

    Posted 06-28-2019 02:25
    ... you will need to create a field in table called [Yesterday]
    with a formula of
    Today()-Days(1)

    Use that field to populate the Key field of the Table B Audit logs the Automation will run each day about midnight "the next day".


  • 8.  RE: Log all data in fields from all records in Table A to a single new record in Table B

    Posted 07-01-2019 14:14
    Awesome, thanks for the replies! I will try your solution out Mark and let you know if I get it to working