Forum Discussion

JosephMahon's avatar
Qrew Member
12 months ago

Extracting Parts Of Text

I have a formula text field that displays a summary of all billing fields.  It combines the billing code, the description of the code, and how many units have been billed.  We used to manually enter these into a 3rd party system, but we are building out an API and need to separate parts of each line into it's own field.  It looks like this:

[Billing Summarized]:

97110 Therapeutic Exercise 1 Unit(s)
97112 Neuromuscular Reeducation 1 Unit(s)
97530 Therapeutic Activity 1 Unit(s)
97140 Manual Therapy 1 Unit(s)

 With the above example I need:

[Line 1]:97110

[Line 1 Units]: 1

[Line 2]: 97112

[Line 2 Units]:1

[Line 3]:97530

[Line 3 Units]: 1


Joseph Mahon

3 Replies

  • Can you explain how the [Billing Summarized] field is created.?  Is this a formula field?  Can you post the formula?   How many lines can there be in the summary?

    Also is he goal to push a button and create child table records one per Billing Line?

    Mark Shnier (Your Quickbase Coach)
    • JosephMahon's avatar
      Qrew Member

      Oh boy I hope you are ready for a little messy and something we have been patching together as we progress. 

      The [Billing Summarized] field is created by a long bulky formula to summarize all of the potential billing fields that get entered.  It looks like this (only longer):

      If([Physical Therapy Evaluation-Low Complexity Billing]!="",[Physical Therapy Evaluation-Low Complexity Billing] & "\n","")&
      If([Physical Therapy Evaluation-Moderate Complexity Billing]!="",[Physical Therapy Evaluation-Moderate Complexity Billing] & "\n","")&
      If([Physical Therapy Evaluation-High Complexity Billing]!="",[Physical Therapy Evaluation-High Complexity Billing] & "\n","")&
      If([Physical Therapy Re-evaluation Billing]!="",[Physical Therapy Re-evaluation Billing] & "\n","")&
      If([Therapeutic Exercise Billing]!="",[Therapeutic Exercise Billing] & "\n","")&
      If([Neuromuscular Reeducation Billing]!="",[Neuromuscular Reeducation Billing] & "\n","")&
      If([Therapeutic Activity Billing]!="",[Therapeutic Activity Billing] & "\n","")&
      If([Gait Training Billing]!="",[Gait Training Billing] & "\n","")&
      If([Manual Therapy Billing]!="",[Manual Therapy Billing] & "\n","")&

      The fields above are generated from other fields as well that look like this: 

      If([Physical Therapy Evaluation-Low Complexity Units]>=1, "97161 Physical Therapy Evaluation-Low Complexity " & [Physical Therapy Evaluation-Low Complexity Units] & " Unit")

      You can see I have the actual individual fields already.  The issue is that when we map them with the API there cannot be blanks.   There can be up to 6 lines total on a claim.  So I ultimately need to translate whatever gets billed to Claim 1, Claim 2, Claim 3, Claim 4 etc and we need it to only be the code and the units billed example:

      97110 Therapeutic Exercise 1 Unit(s) 

      Claim Code 1: 97110

      Claim Code 1 Units: 1

      Joseph Mahon
      • MarkShnier__You's avatar
        Icon for Qrew Legend rankQrew Legend

        I think it's possible to do,  but I cant think of quick and elegant solution.  There may be some magic Jinja code which can be used in a Pipeline, but I'm not good enough with Jinja to write that code.

        I'm sure I could write a formula uURL button to push to create the  children by extracting the individual parts of the max six lines or else a possibly a cleaner approach would be to make a mirror of the current formula to make it into a CSV file structure and use an API to import CSV. 

        But either way it would probably take an hour of consulting to get that working.  Contact me directly by the email in my signature line if you want one on one assistance with that.

        Mark Shnier (Your Quickbase Coach)