Forum Discussion

DanielJohnson2's avatar
DanielJohnson2
Qrew Trainee
3 years ago

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

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
------------------------------
Pipelines
Relationships
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    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
    ------------------------------
    • DanielJohnson2's avatar
      DanielJohnson2
      Qrew Trainee
      3 years ago
      Hey Mark,

      Thanks for the reply, as always. I see where you're going with that User Focus table and I decided to try it out with a combination of pipelines, formula queries and API calls.

      So, I've got a pipeline that creates a dummy record in the schedule table under my username for each day (the same pipeline is meant to delete old records). Then, when I'm ready to view the recording record I'm starting from a report from the recordings table with a button on it. I added a variable with a query in it in the button field to locate the dummy record and send over the length of the recording when I press the button from the report.

      Then back in the schedule table, I've got another formula query field that is looking only at that dummy record and the field with the recording length in it that gets updated every time I click that button.

      So now, when I click that button on the recordings table report, it opens the recording record with the linked report in it, that now has a field that's looking at that dummy record that has the length of the current recording in it just sent over with the button click, and from there I can do my calculation.

      I am having a little trouble with the pipeline at the moment. I want to scrub the schedule table of the dummy records. So, my thought was to create a scheduled pipeline that creates a new record two days out, every day, and deletes yesterday's record. However, I'm having trouble with the Search Records step identifying my dummy record from yesterday. Any thoughts there?

      Thanks,

      ------------------------------
      Daniel Johnson
      ------------------------------
      • DanielJohnson2's avatar
        DanielJohnson2
        Qrew Trainee
        3 years ago
        Figured out the Pipeline issue. I was putting a {1.EX.'yesterday'} type expression in the advanced expression in the conditions area, when it was meant to go directly under the fields in the Advanced Query area. Works now.

        ------------------------------
        Daniel Johnson
        ------------------------------