Forum Discussion

JordanMcAlister's avatar
JordanMcAlister
Qrew Captain
6 years ago

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

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!




APIs and custom code
Data import and export
Formulas and functions
  • DarioMassaro's avatar
    DarioMassaro
    Qrew Member
    6 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    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.   
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      6 years ago
      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.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      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.


       
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      ... 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".