Counting lines in a longtext field

[GSFife]

Hi,

I have an appsheet application that is used by PowerBI for reporting.  The space in the Powerbi report is 6 lines of 94 characters per line. 

I can set a total character count of 564, but if the user adds a line break to start a new line, they can exceed the 6 lines within the 564 count.  As an added complication the field size in appsheet is different than Powerbi, so you could have a 120 character line, which would take up 2 lines in Powerbi, and even if I could get the line count working, Appsheet would only see this as 1 line.  So ideally I need to set the data validity to determine if there is more than 6 lines for Powerbi (ie if a "line" in appsheet is >94, it would treat it as 2 lines, > 188 treat it as 3 lines etc)

Any thoughts?

I did see this thread from 5 years ago, but for some reason the code used within this does not work, appsheet just does not seem to see the new line character, 

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Count-the-number-of-lines-used-in-a-longtext-fi...

I also tried asking Gemini and after about an hour or refining and it keeping repeating things that didn't work or suggesting I used CHAR(10), which Appsheet doesn't recognize as a function, Gemini admitted defeat!

Any help greatly received!!

[TeeSee1]

Would it be too out of place to have six TEXT (not LONGTEXT) fields of 94 max characters and concatenate them to pass to PowerBI?

[GSFife]

Good thought TeeSee, but from a user perspective it would not be good - its a comments field, so would be a bit unwieldy for the user to use

[TeeSee1 · Accepted solution]

Or you can try this to count the lines in terms of the PowerBI report.

CEILING(LEN(index(SPLIT([fld],"
"),1))/94)
+
CEILING(LEN(index(SPLIT([fld],"
"),2))/94)
+
.
.
+
CEILING(LEN(index(SPLIT([fld],"
"),7))/94)

This expression works in a regular field, valid-if but not as a VC for reasons unknown....

These are screen shots of some test data using this formula (allowing up to 3 lines of 30 char long each)

"Line count is " &
(
CEILING(LEN(index(SPLIT([fld2],"
"),1))/30)
+
CEILING(LEN(index(SPLIT([fld2],"
"),2))/30)
+
CEILING(LEN(index(SPLIT([fld2],"
"),3))/30)
+
CEILING(LEN(index(SPLIT([fld2],"
"),4))/30)
)
& 
". It should not exceed three."

[GSFife]

This worked a treat, thanks TeeSee1!