Skip to main content
Question

Use field from current record in a formula field on linked report?

  • December 23, 2021
  • 0 replies
  • 32 views
Hi All,

Is it possible to use a field from the current record (I'm on a form) in a formula on a linked report? In this case, I've got a record with the length of a recording. The linked report is the schedules of people available to work on that recording. In that report is their start time, end time and their daily capacity. With that info I can calculate their speed per recording minute on that schedule table. Combining their speed from the schedule table and the length of the recording from the recordings table will tell me when to expect the work on that recording (generally) to be completed. Is there a way to do this?

Thanks,

------------------------------
Daniel Johnson
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+22
  • MarkShnierYou
  • Quickbase Alumni
  • December 23, 2021
No,  but yes.
Since I assume that you do not have a relationship  and all you have is a report link field on the form, then No, the records on the report link have no way to know what record you are looking at to use one of its values in a calculation.

So Yes, there is a way.

I create a table called User Focus where the Key field is the userid.  I add a formula checkbox field there called User Exists? with a value of TRUE.  So it's always checked. Then add a field to hold the [Record ID#] of the focus Recording length record.  Let's say that is fid 8. Also make a field to hold the [Focus Recording Length]

Then create a relationship where 1 User Focus has Many Recording Length Records. Let is create a field for the relationship and then change that field to be called Current User, and make the formula User() .. ie the Current user. Lookup the field for [User exists?] down to Recording Length record.

Then create a formula URL button on Recording Length record to either edit or create a user focus record 

//Edit or Create a User Record for the Current User (remember to set permissions in the User focus table)
//Set Key Field
//User Exists - true!

//Remember to set permissions

var text AddUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_AddRecord"
& "&_fid_6=" & ToText(User())
& "&_fid_8=" & ToText([Record ID#])
& "&_fid_9=" & ToText([Recording Length]);

var text EditUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_EditRecord"
& "&key=" & ToText(User())
& "&_fid_8=" & ToText([Record ID#])
& "&_fid_9=" & ToText([Recording Length]);

var text ReDisplayRecord  = URLRoot() & "db/" & dbid() & "?a=dr&rid=" & [Record ID#];

If([Current User - User Exists?],
$EditUser& "&rdr=" & URLEncode($ReDisplayRecord),
$AddUser& "&rdr=" & URLEncode($ReDisplayRecord))

Great now look up the [Focus Recording Length Record ID#] down to the recording length table as well as the [Focus Recording Length ]and have a form rule to not show the report link field unless the button has been clicked to put the Recording length record in focus for the current user.  

Now make a relationship from User Focus down to the Schedules Table based again on a Formula User field called [Current User] with a formula of User() and lookup the recording length value.

Now you can do your calculation!

I call this technique the User Focus method as it allows for multiple simultaneous Users to have their Focus Record record the Record ID# they are on and use values from that focus Record in any other table. 




------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Terms & ConditionsAccessibility statement