I would like to move away from an Excel VBA data collection method into Power Apps for data collection. The VBA method uses the syntax found at https://riptutorial.com/vba/example/15513/declaring-fixed-length-strings
Dim FixedLengthString As String * 5 ' declares a string of 5 characters
I have not been able to find any Power Apps technique to reproduce the desired output (.dat)
For now I suppose focus on [Journal ID] & [Journal Date] = {1..37} and 'B' in "BUDGETS" is 38:
Any ideas?
Solved! Go to Solution.
@JR-BejeweledOne- Too funny! I am about to cry because your last two messages remind me that it's the little things that matter. So message 5, reminded me I went down that path. I reviewed my 'Screen2' and said to myself, "nah that doesn't work". The kicker was what you said in message 6, "...use a sequence function to sequentially add a single space..." BAM! I totally missed the first space!
Concat(
Split(
Text("XXXXXX" & lblJournalID & lblJournalDate & "N") & Concat(
Sequence(37 - Len(Text("XXXXXX" & lblJournalID & lblJournalDate & "N"))),
" "
),
""
),
Result,
""
)
While I don't have an exact answer for you, you will find a lot of information about the available text functions here.
https://matthewdevaney.com/power-apps-text-functions-with-examples/
@JR-BejeweledOne- he's great for sure; provides tons of content for the community! I think one of his articles helped using SEQUENCE( ), SPLIT( ), and CONCAT( ) which I used for [Journal ID] as:
If(
Len(
Left(
User().Email,
Find(
"@",
User().Email
) - 1
)
) > 8,
Upper(
Concat(
Split(
Left(
User().Email,
8
) & Concat(
Sequence(2),
Text(Value) & ""
),
""
),
Result
)
),
Upper(
Concat(
Split(
Left(
User().Email,
Find(
"@",
User().Email
) - 1
) & Concat(
Sequence(2),
Text(Value) & ""
),
""
),
Result
)
)
)
Turns out though 8 chars + 2 digits isn't random and is supposed to be sequential 2 digits...Hence, why my plan is to capture the 'transaction' using Power Automate saved in SharePoint (likely) to filter count of Items by 'uploader'.
My bigger problem though is the output has to be fixed width so it doesn't matter if I can get sequential numbers until the .dat file can successfully upload into the system of record.
@JR-BejeweledOneor other Super Users, can you syndicate this to the Power Automate community as I am not opposed to a data transformation solution in Power Automate. Your fellow 'Power BI Super User' would appreciate it 👍
So I just did a test using both a text input and an html text to make sure that spaces are counted.
Here is the formula you need to use against those. For the first value use any number larger than 5. Then you can do an If statement with the formula below and use some text functions like split after the 5th character to truncate or replace the value as text like so, somevalue = textinput.text & " " or however many spaces you need, but I think this will give you the start to figure it out. This formula gives you the difference between the number you specify and the number of characters in your text box or field.
Text(15 -Len(TextInput7.Text))
If you used 7 for example, as your first number then you could conceivably use a sequence function to sequentially add a single space to the value to get what you need.
@JR-BejeweledOne- Too funny! I am about to cry because your last two messages remind me that it's the little things that matter. So message 5, reminded me I went down that path. I reviewed my 'Screen2' and said to myself, "nah that doesn't work". The kicker was what you said in message 6, "...use a sequence function to sequentially add a single space..." BAM! I totally missed the first space!
Concat(
Split(
Text("XXXXXX" & lblJournalID & lblJournalDate & "N") & Concat(
Sequence(37 - Len(Text("XXXXXX" & lblJournalID & lblJournalDate & "N"))),
" "
),
""
),
Result,
""
)