ContributionsMost RecentMost LikesSolutionsRe: Anyway to convert RichText or Multi-Select Text fields into a numerical Value? (trying to Query of a result)Thank you, Mark, that did it! ------------------------------ Liz Chartrand ------------------------------ Anyway to convert RichText or Multi-Select Text fields into a numerical Value? (trying to Query of a result)Hello, I am getting stuck trying to make a rich text/multi-select text result value into an actual numeric value , I have the following Formula rich Text Field which will result in a specific dollar amount, var text NextLease = "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& [Query Next Valid Lease (Renewals)] &"'}"; SearchAndReplace(ToText(GetFieldValues(GetRecords($NextLease,[_DBID_RENTAL_DETAIL]),69)), ";" , "<br>") Alternatively, this is what I have for the Multiselect version of this field var text NextLease = "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& [Query Next Valid Lease (Renewals)] &"'}"; SearchAndReplace(ToText(GetFieldValues(GetRecords($NextLease,[_DBID_RENTAL_DETAIL]),69) However, this isn't a true value, it looks like a dollar amount, but the system is not fooled! Is there a way for me to convert the rich text or multi-select text into a numeric value? Very interested in any suggestions! Thank you! ------------------------------ Liz Chartrand ------------------------------ Re: Size Query - Ranking and then Querying the Next Ranked RecordHello again, I realized what the issue was and with a little more discussion came up with the following, 1) Rank records Chronologically (in my case, it's based on a Lease start date and omits records that do not fit criteria) var text CTR = "{6.EX.'" &[Related Unit]& "'}AND{39.LTE.'" & [Lease Start] & "'}AND{142.XEX.'Application in Process'}AND{168.EX.''}"; If([Lease Status]="Application in Process", null, If(ToText([Lease Canceled])<>"",null, If([Resident Transferring to Another Unit]="Yes",null, Size(GetRecords($CTR))))) 2) Then, using that Query field as a reference, Find the next valid Ranked record (includes same omissions) *but is sort of limited to a specified number of records which may prove problematic in the long term: var text NextOne = "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+1) &"'}"; var text NextTwo = "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+2) &"'}"; var text NextThree = "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+3) &"'}"; [...] var text NextTen = "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+10) &"'}"; var text NextOneCheck = ToText(GetFieldValues(GetRecords($NextOne),475)); var text NextTwoCheck = ToText(GetFieldValues(GetRecords($NextTwo),475)); var text NextThreeCheck = ToText(GetFieldValues(GetRecords($NextThree),475)); [...] var text NextTenCheck = ToText(GetFieldValues(GetRecords($NextTen),475)); Min( $NextOneCheck, $NextTwoCheck, $NextThreeCheck, [...] $NextTenCheck) here's how all that looks in a report: A single unit (203), with lease records organized/ranked according to lease start dates, (Lease 1 and Lease 2), then from lease 1, you can reference lease 2 For actual application of this I then used another query field to pull in values for Gross rent: var text NextLease = "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& [Query Next Valid Lease (LLCR Renewals)] &"'}"; SearchAndReplace(ToText(GetFieldValues(GetRecords($NextLease,[_DBID_RENTAL_DETAIL]),69)), ";" , "<br>") ---- I think this is a decent solution for the moment, but for step 2 do you have any suggestion for a better more ever-green way to write this out? Thank you for your help! ------------------------------ Liz Chartrand ------------------------------ Re: Size Query - Ranking and then Querying the Next Ranked RecordHi Quick Base Junkie! Thank you for your response! I think I'm still getting stuck with my rank (Size Query Field) in tandem with a GetFieldValues() field. Here's what I have so far for my Rank: var text NRTR = "{6.EX.'" &[Related Unit]& "'}AND{39.LT.'" & [Lease Start] & "'}AND{142.XEX.'Application in Process'}AND{168.EX.''}AND{261.XEX.'Yes'}"; If([Lease Status]="Application in Process",null, If(ToText([Lease Canceled])<>"",null, If([Resident Transferring to Another Unit]="Yes",null, Size(GetRecords($NRTR))+1))) -or this- var text NRTR = "{6.EX.'" &[Related Unit]& "'}AND{39.LTE.'" & [Lease Start] & "'}AND{142.XEX.'Application in Process'}AND{168.EX.''}AND{261.XEX.'Yes'}"; If([Lease Status]="Application in Process",null, If(ToText([Lease Canceled])<>"",null, If([Resident Transferring to Another Unit]="Yes",null, Size(GetRecords($NRTR))))) This is attempting to omit a few Statuses, which it is doing, but I'm noticing the Rank, when looking at a list of records in a report is: null, 1, 3. Do you have any suggestions on how to make it null, 1, 2, for a group of 3 records that includes a record that should be ignored/omitted from being ranked? Or, is there a way to salvage this in the next GetFieldValues() For instance, If I need to stick with null,1,3, How might I write a GetFieldValues() Query that pulls a value from the Rank 3 Record, from the perspective of the Rank 1 Record? Thank you for your help! ------------------------------ Liz Chartrand ------------------------------ Size Query - Ranking and then Querying the Next Ranked RecordHello! I recently figured out how to rank records in QB by using the Size function to organize records chronologically. I'd like to take that a step further and be able to rank records that fit a set of criteria and then query the very next record in the ranked sequence. In other words, if I have 3 records Ranked 1, 2, 3 - I'd like to be able to query information on Record 2 from Record 1 and, I'd like to be able to reference Record 3's information on Record 2. If anyone has any suggestions, even if it's not specifically the size function, I'm all ears! Thank you! ------------------------------ Liz Chartrand ------------------------------ Re: Scheduled Pipelines - Suggestions for how to create records daily to archive a table's activityThank you, Mark! I'll be honest, I'm a little embarrassed haha, I've been able to set this up very easily, I don't know why I didn't test out the search option when I was digging in yesterday. So far so good, hopefully, that trend continues! Thanks again! ------------------------------ Liz Chartrand ------------------------------ Re: Creating Survey - Rating FieldLisa Sawyer had a great Empower 2021 session (https://youtu.be/eBoX594-9Zw) that I think may help in addition to the Magic Buttons app. Although it is indirectly related, I think the app and the session video can help to get you to what you want to build. She also includes a ton of additional information and resources that may be helpful. ------------------------------ Liz Chartrand ------------------------------ Scheduled Pipelines - Suggestions for how to create records daily to archive a table's activityHello, My original goal was to set up an automation for this task and then migrate to pipelines to see if I could follow the logic. It turns out you cannot migrate scheduled automation, which makes sense to me now since there is no trigger just a time and a place. In any event, my goal is to copy all records of a table (Leasing units table) into another table on a daily basis, in order to capture all changes and adjustments into an archive of all activity. I'm having trouble setting up the pipeline - I've figured out how to set up the scheduling part, but I'm not sure how to get the fields from the Units table into the Unit Archive/History table. Does anyone have a suggestion about how to just copy records into another table on a daily basis? Seems simple enough, so I'm definitely missing some foundational understanding. Thank you for your suggestions, Liz ------------------------------ Liz Chartrand ------------------------------ Re: Rich Text Multivariable Button w/ CSS color HelpOh excellent! Thank you ------------------------------ Liz Chartrand ------------------------------ Re: Rich Text Multivariable Button w/ CSS color Helpah ha! That was it - A space before the hrefs was missing. We are in business - Thank you, Sharon! ------------------------------ Liz Chartrand ------------------------------