Forum Discussion

DeepaPrashant's avatar
DeepaPrashant
Qrew Member
10 months ago

Related Filtered field

Hello,

I have an app where I have two tables for Location Codes. One is a sync up with SAP for All active Location Codes and the second table (Location Code request) has a form to Change or request the creation of a new Location Code. In the Location Code request form I also have a STATUS field that tells us if the request is New, in-process or Completed. Of-course if it is completed, it would mean the New Location Code has been created in SAP with the next sync up run (which is daily) and my SAP for All active Location Codes table will now include that New Location Code record from Location Code request table.

Now, I have another table called Asset forms where users can create an Asset Capitalization request and as part of that form can choose from the 'existing' (SAP for All active Location Codes) table or choose from an 'in-process' (Location Code request) list. In the in-process list, we did not want the Location Code requests that had a status of 'Completed' as these should ideally be picked from the SAP for All active Location Codes table. So I used Quickbase action to create a duplicate of the Location Code request table but filtered for anything other than "Completed" Status (lets call this Duplicate Location Code requests).

So the problem I am having is that when a Location Code request gets marked as Completed, the Asset form loses the Location Code request choice as that no longer lives in that table Duplicate Location Code requests when it gets updated via Quickbase actions (again daily). Was hoping to create a field that would say if the record number of Duplicate Location Code requests is 33. I would then need to freeze that Record ID# '33' and then lookup Record ID# 33 in Location Code request. As Location Code request table will hold all values, the chosen Location Code will continue to inhabit in the Asset form.

Or is there a better easier way to achieve this? 

Thanks!

Deepa



------------------------------
Deepa Prashant
------------------------------

  • Hello,

    I tried to use a Formula Query hoping to pull up the corresponding record from the Location Code request table. My query is as shown below:

    GetFieldValues(GetRecord([Location Code in Process RecID],[_DBID_REQUEST_A_NEW_LOCATION_CODE]),103)

    where [Location Code in Process RecID] would be the corresponding Record in Location Code request table

    but get an error 

    This is my first time using a Formula Query. What am I doing wrong?

    Thanks!



    ------------------------------
    Deepa Prashant
    ------------------------------
    • DeepaPrashant's avatar
      DeepaPrashant
      Qrew Member

      Hello,

      I am making progress! I was able to get my formula query to work by just converting the type to text. But how do I now take a snapshot of either [Location Code in Process RecID] or the actual Record in Location Code request table which gets pulled up initially upon choosing the in-process location code? Once the in-process location code gets marked as completed, that value will disappear and so will the formula query value?

      Thanks!



      ------------------------------
      Deepa Prashant
      ------------------------------
      • DeepaPrashant's avatar
        DeepaPrashant
        Qrew Member

        Figured this one out!!

        Created a snapshot of the original related field and changed my formula query to use that. Worked like a charm.

        Sometimes it helps to write out your question. :-)



        ------------------------------
        Deepa Prashant
        ------------------------------