Discussions

 View Only
Expand all | Collapse all

View only the last 4 lines in a data field?

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training04-18-2019 13:51

  • 1.  View only the last 4 lines in a data field?

    Posted 04-18-2019 13:49
    Hi, I'm new to QuickBase and I'm having trouble with a formula for a text field. We have data line 1-7, but I only want to pull the last 4 lines in the data (data lines 4-7). I've tried using the part & right formula, but I can't seem to make it work. Any ideas?

    Here's the formula we've been using, but I need the last 4.
    Right(Right([Long Text],"\n"),"\n")

    Thanks!


  • 2.  RE: View only the last 4 lines in a data field?

    Posted 04-18-2019 13:51
    Can you post an example of what your data looks like?


  • 3.  RE: View only the last 4 lines in a data field?

    Posted 04-18-2019 14:45
    Yes, here it is below. We want to view only the 2/3/2019 message.

    05/04/2018 09:24:39 MST  EP1RFCXP1 (EP1RFCXP1) 
    ||elevation:5400|| INDIANA/91ST (WEST SIDE OF INDIANA) -ELEC SVC UG 1PH 
    120/240 GAS SVC - NO LOAD INFO PROVIDED WITH APPLICATION. CONTACT INFO: 
    JANE DOE 999-999-999 JEFFERSON CENTER METRO DISTRICT 
     
    ContactType: OWNR 
    ContactName: JEFFERSON CENTER METRO DISTRIC 
    TelephoneNumber: 999-999-999 
    PhoneNumberExtension: 
    Address: 1900 Misc street,LAKEWOOD,CO,80228-1814 
     
    01/31/2019 12:30:53 MST Kristi R Clark (230257) 
    design and project transferred to a new designer. waiting on customer verification for route.

    2/3/2019 12:55:04 MST Krisit R Clark (230357)
    Contacted customer and left Voice Message. 


  • 4.  RE: View only the last 4 lines in a data field?

    Posted 04-18-2019 20:47
    Is the delimiter (for humans) to decide when the next message starts that initial date field?

    So in the text above there are three separate messages and you want just the last one, but any message can be any length?


  • 5.  RE: View only the last 4 lines in a data field?

    Posted 04-18-2019 21:57
    Yes, that's correct. The field is free form that we pull from SAP and then we import that field from excel into Quickbase. 

    Several messages in that field and we want to just pull the last one. The message can be any length. 

    Thanks!


  • 6.  RE: View only the last 4 lines in a data field?

    Posted 04-18-2019 22:43
    I�m sure it is possible and I will play with it over the next couple days as I have time. I�m sure we can hook onto those semicolons, walk backwards to the first; and then grab the rest of the date field.


  • 7.  RE: View only the last 4 lines in a data field?

    Posted 04-19-2019 13:47
    Here is a formula which seems to work.  But it does make the assumption that the time stamps all the MST in them.  Is that a valid assumption?

    var text StepOne = SearchAndReplace([My text field],"MST","|");

    var text Message = Right($StepOne,"|");

    var text RestOfMessage = NotRight($StepOne,"|");

    var text DateStamp = Right($RestOfMessage,"\n");

    Trim($DateStamp & " MST " & $Message)


  • 8.  RE: View only the last 4 lines in a data field?

    Posted 04-19-2019 13:59
    Even if they have other time zones, you could add variables to successively replace them all with the delimiter.


  • 9.  RE: View only the last 4 lines in a data field?

    Posted 04-19-2019 15:46
    Good point!


  • 10.  RE: View only the last 4 lines in a data field?

    Posted 04-22-2019 13:58
    Hi, Thank you for the code. We tried using this code and we get a formula error. 
    A formula may not contain a reference to itself directly or indirectly through another formula. 

    Here's the updated code I used. 
    var text StepOne = SearchAndReplace([Long Text Formula],"MST","|");

    var text Message = Right($StepOne,"|");

    var text RestOfMessage = NotRight($StepOne,"|");

    var text DateStamp = Right($RestOfMessage,"\n");

    Trim($DateStamp & " MST " & $Message)

    Thanks!


  • 11.  RE: View only the last 4 lines in a data field?

    Posted 04-22-2019 14:00
    I suspect that you need to change this

    var text StepOne = SearchAndReplace([Long Text Formula],"MST","|");

     to this

    var text StepOne = SearchAndReplace([Long Text],"MST","|");


  • 12.  RE: View only the last 4 lines in a data field?

    Posted 04-22-2019 14:55
    Hi, It worked. Thank you so much! 

    I appreciate your time. 


  • 13.  RE: View only the last 4 lines in a data field?

    Posted 04-18-2019 17:04
    Hi Catelyn. You're running into a common problem resulting from what's called a de-normalized database. It's a technical concept, but basically in your case you have several records worth of information stored in a single field, and so it's hard to retrieve just a portion of that field to display somewhere else.

    Another example of the same kind of thing (but much simpler and therefore easier to resolve) is if you have a person's full name stored in a single field, like "Sam Q. Jones IV" and want to extract only the last name or the middle initial.

    I don't know of a way using a native Quick Base formula to return only the most recent message, especially if there are an unlimited number of messages appended to the field, and they vary in length and formatting.

    The best solution is probably to normalize the database by adding a child table that will hold a single record for each one of these messages. From there, it's easy to surface only the most recent message on a form or in a report either by using a Report Link field or a reverse relationship.

    I'm happy to help further if you need it.

    Yours truly,

    Phillip


    PHILLIP DENNIS
    Principal | Watkyn LLC
    Quick Base Solution Provider
    (954) 900-6690 | hello@watkyn.com
    www.watkyn.com


  • 14.  RE: View only the last 4 lines in a data field?

    Posted 04-18-2019 19:38
    Caitlyn, can you clarify the ask here. In the example above what is the result you are looking for?