Is it possible to populate fields in one table with data from fields of another table?

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
  • Answered

Before I start trying and make a mess I wanted to ask.... 

Here is my situation: I have a tasks table and a weekly agenda table. Employees would like to be able to click a button on a task and move it to the Weekly Agenda table and maybe add more data once in the weekly agenda area. (They want it to 

Rather than having a relationship with reports links, etc... I would like employees to be able to click the button and have the relevant information auto-populate on the new form... I know this can be done within a table, but I dont know if it is possible to do ACROSS tables... 

Any suggestions? 

Photo of Amanda

Amanda

  • 10 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Not tested, but try this and we can debug together if there are syntax errors.

Urlroot() & "db/" & [_DBID_of the target table] & "?act=API_AddRecord"
& "&_fid_10=" & URLEncode([Subject])
& "&_fid_11=" & URLEncode([Topic Details])
& "&disprec=1"

So that will create a new record in the weekly agenda table and stuff the values of two fields on a task record into field IDs 10 and 11. Then it will display the Weekly Agenda record. The user would then edit the record manually to fill in the rest of the fields.
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb
I am trying to something very similar but with a date field.  I want Date of Incident from the Incident Submittals table to populate into the Date of Incident (fid=51) in the Cases table. I have the following formula in my URL formula field but keep getting an error.

URLRoot() & "db/" &[_DBID_CASES] & "?act=API_AddRecord"
& "&apptoken=My App Token"
& "&_fid_51=" & URLEncode([Date of Incident])
& "&disprec=1"

Error Message:
This XML file does not appear to have any style information associated with it. The document tree is shown below.

<qdbapi>
<action>API_AddRecord</action>
<errcode>50</errcode>
<errtext>Missing required value</errtext>
<errdetail>
Sorry, you did not supply a value for the required field named "Reported By Channel" with field id 13. Please provide a non-blank value for this field.
</errdetail>

</qdbapi>


If it opened the record in front of me I would enter the information in the required fields it's saying I need. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Stephanie,
Are you trying to create a record or put up an Add Record form with some of the field pre-populated.
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb
I would like to create a new record on the Cases table with some fields using data populated from the Incident Submittals table.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
If you are looking to actually create the record and then display the record to the user, then you will need to supply all fields which are set as being Required, in their field properties.

If you are wanting to put up an Add Record Form and have the user create the record by manually saving it, then there is a different API for that.
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb
I don't want the new case saved automatically, I want to bring up the add record form for a new case with some fields being filled in with data from the related incident submittal.  Then the person can manually fill out the required fields on the Cases table and save it.  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
OK, so the other API syntax to put up an Add Record form is this


URLRoot() & "db/" &[_DBID_xxxxxx] & "?act=API_GenAddRecordForm"
& "&_fid_51=" & URLEncode([Date of Incident]);
& "&_fid_52=" & URLEncode([some other field]);
& "&_fid_53=" & URLEncode([yet another field]);

 You get the in the [_DBID_table name] using the Advanced Properties 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Just a correction to the above - we should not have the semi colons.



URLRoot() & "db/" &[_DBID_xxxxxx] & "?act=API_GenAddRecordForm"
& "&_fid_51=" & URLEncode([Date of Incident])
& "&_fid_52=" & URLEncode([some other field])
& "&_fid_53=" & URLEncode([yet another field])
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb

I can get this to work with 1 field but when try to add another field I get the error

"There are extra characters beyond the end of the formula"

URLRoot() & "db/" &[_DBID_CASES] & "?act=API_GenAddRecordForm"
& "&_fid_51=" & URLEncode([Date of Incident])  ;
& "&_fid_62=" & URLEncode ([Brief Description])


My formula is:

URLRoot() & "db/" &[_DBID_CASES] & "?act=API_GenAddRecordForm"

& "&_fid_51=" & URLEncode([Date of Incident]);

& "&_fid_62=" & URLEncode ([Brief Description])


Also, I was hoping this would be created as a related record.  I want to be able to see what Incident Submittal this Case was created from, I do have the relationship set up: 1 Incident Submittal could have many cases

Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb

I edited the formula with the correction above and it is working.  I just need to get the case related to the incident submittal

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Right, so if you are launching off a Parent to create a child, you need to be sure to populate the field called [Related Parent] with [record ID#] (ie the [Record ID#] of the parent you are sitting on when you click the ADD Child).   Otherwise the child will be an orphan without a Parent.
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb
So I created a field on the Parent (Incident Submittals) table called Incident # which is a formula and is just the Record ID#. Is there something I add to the formula that tells the Child table (Cases) that it's related to his Parent?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
you need to add in this line

& "&_fid_999=" & [Record ID#]

where 999 is the field id of the field on the child record for [Related Parent] - it is probably called [Related Incident Submittal]
Photo of Stephanie

Stephanie

  • 732 Points 500 badge 2x thumb
Perfect! Thank you!
Photo of Amanda

Amanda

  • 10 Points
Wow! That was the most unexpectedly easy Quickbase task I have done yet!
This just about worked!! No syntax errors.. The only thing I had to do was add an App Token. So my final formula looked like this:

URLRoot() & "db/" &[_DBID_EMPLOYEE_TASKS] & "?act=API_AddRecord"
& "&apptoken=c8usqfibak5cz4c2vx5fhdh7c9dv"
& "&_fid_6=" & URLEncode([Subject])
& "&_fid_10=" & URLEncode([Description])
& "&disprec=1"


THANK YOU!!!!!
Photo of Amanda

Amanda

  • 10 Points
Actually I do have 1 question... Is it possible to open the form on the other table in EDIT mode versus VIEW mode?
Photo of Ryan Stanford

Ryan Stanford

  • 1,156 Points 1k badge 2x thumb
I know this is an old post... but yes, you can have it open the form in edit mode, generally with adding a redirect at the end of the parameters