Forum Discussion

EmberKrumwied's avatar
EmberKrumwied
Qrew Captain
3 years ago
Solved

Formula Field and Pipeline Issue

Me again...: )

I have a formula field that combines the selections in 2 different fields.  The result of which is copied to another field when the form/record is saved.  (This is to create a relationship filed to another table.) - This step works as expected.

I have a checkbox that looks to see if there is a record in the related table for the value in the relationship field.  Unchecked, no record.  Checked, record. - This step works as expected.

I have a pipeline that is set that when a record is created, if the box is unchecked, then create a new record in the related table using the relationship field as the primary key.  No other fields from the parent table are copied. - This step appears to start too "early" as the resulting created records do not have the correct relationship field.

Scenario - user selects create new record and must select a Property and then select a Year.  The formula field on the form is a simple concatenation ([Unit - Related Property/Asset]&" - "&[Policy Year]).  The formula field will update once a Property is selected and then again when the Year is selected.  - This step works as expected.

The checkbox field looks to see if the combined field has a related record on another table and will be checked if so and remain unchecked if not.  - This step works as expected.

I believe the problem is that the pipeline appears to be kicking off after making the first selection instead of when the user actually saves the record.  I assumed that the record isn't "created" until you actually save it as you could cancel it...but that does not seem to be the case.

So first, I guess I need confirmation on when a record is "actually" created per pipeline definitions.  If it is actually when the add new record button is selected or when the first selection on a form is made, then I will need to determine a work around.

If the fault is lying with the formula field itself, is there anyway to tell a formula field when to actually "formulate"?  Meaning, can I somehow tell that field to only calculate on closing the form?

Thanks



------------------------------
Ember
------------------------------
  • EmberKrumwied's avatar
    EmberKrumwied
    3 years ago

    After some more testing I have realized that the field the formula is looking to does not get populated until the record is saved (thus the "0" value).  This in turn causes the form rule save feature to copy the wrong value.  Thus, the pipeline kicks off and creates a new record but uses the wrong value.  So I changed the formula to pull the needed information from a different field and it all appears to be working as expected.



    ------------------------------
    Ember
    ------------------------------

6 Replies

  • MikeTamoush's avatar
    MikeTamoush
    Qrew Elite
    3 years ago

    The Pipeline will only kick off upon saving. A new record is created as soon as it is saved the first time, independent of what is being selected on the form.

    However, I don't think you ever actually said what the issue is. Sounds like your pipeline is kicking off - is the problem something to do with the related field not being filled in correctly? Can you explain the actual issue you are running into?



    ------------------------------
    Mike Tamoush
    ------------------------------

    • EmberKrumwied's avatar
      EmberKrumwied
      Qrew Captain
      3 years ago

      The issue is the pipeline is running as soon as I make the first selection.  It is creating a new record, as expected, but with only part of the primary field I need.

      An example is Property selected = 113, Year selected = 2022-2023.  Formula field calculates to "113 - " after making the Property selection.  Checkbox is unchecked as there is no match in the related table, so pipeline runs and creates a new record with 113 -  as the primary key.  This is incorrect.  The primary key should be the combination of Property and Year (113 - 2022-2023).  Since the new related record's primary key is only 113 -  it does not link back to my parent table and thus the checkbox is never checked.



      ------------------------------
      Ember
      ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago

    Wow, this is surprising.  For sure a Pipeline does not start until the record is actually saved.  It's hard to believe that the combined field is not populated when the record first saves as it only saves once, and presumably, you are saying that it saves correctly.

    I have two thoughts. One is that instead of triggering when the record is saved, trigger the rule when the formula field is not equal to the data entry field and deselect the check box at the bottom of the form rule so that the rule fires all the time.

    The other thought really should not be necessary,  because its hard to believe that the record is not being saved correctly, but the Pipeline could trigger and then it could have a step to lookup the trigger record and then have an Conditional Branch to create the parent if needed. That would delay the actual Pipeline to pull the record details.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • joevandervest's avatar
    joevandervest
    Qrew Cadet
    3 years ago

    One other thought, I've run into this working with chained pipelines, but with the all the pipeline based saving going on, etc. in your example, you might want to think about using the "Pause" in the "Clock" channel.  It might fit for this example. 

    I've used it a couple of times to buy the system record states time to "catch up" before the pipeline continued .. even a couple of seconds can make a big difference. 



    ------------------------------
    joe vandervest
    ------------------------------
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Elite
    3 years ago

    What makes you think it 'appears to be kicking off'? Are you watching the pipeline on another screen and seeing it kick off when you make a selection? Or is it just a troubleshooting guess?

    Are you using the manual field at any point in your pipeline (the field that the formula field is copied to when the record is saved)? If so, my guess is as soon as it is saved it looks at all the fields and uses them in the pipelines. Maybe it all happens so fast that the alternate field is still 'blank' for a split second - meaning that field is not being sent to the pipeline. Alternately, if your relationship is based off that alternate field (I am guessing it is a scalar field), the relationship doesnt exist until after the record is saved, so if your pipeline is dependent on the relationship, it may not exist yet. It may only exist a split second after the pipeline fires.



    ------------------------------
    Mike Tamoush
    ------------------------------
    • EmberKrumwied's avatar
      EmberKrumwied
      Qrew Captain
      3 years ago

      After some more testing I have realized that the field the formula is looking to does not get populated until the record is saved (thus the "0" value).  This in turn causes the form rule save feature to copy the wrong value.  Thus, the pipeline kicks off and creates a new record but uses the wrong value.  So I changed the formula to pull the needed information from a different field and it all appears to be working as expected.



      ------------------------------
      Ember
      ------------------------------