Need help with customized form

[lumberjacklurch]

I all,

I need help (again).  I'm working on a customized form that is linked to a SPO list (obviously).  In the form, I have a gallery, where, if a person is putting in a new entry, can make a list if items to be included in that entry.  The problem I have now, is how to populate the gallery in the customized form when someone clicks on/opens an existing item in the list.  I've tried to use ThisItem.<whatever> in the default values of the gallery, but it doesn't seem to be doing the job.  Any ideas how I can make this work?

[lumberjacklurch]

My main reason is because I don't know how I'd save/recover the data if a user entered more than one item.  Say I were to make columns for each part of the Item Details:

Item Name       |        Item Description        |         Item Quantity

PC                             DELL                                      1

For a single item, I agree, easy.  But for a multi-line record:

Item Name       |        Item Description        |         Item Quantity

PC                             DELL                                      1

MAC                         Apple                                     3

Tablet                       Acer                                       2

How do I put them back into a gallery?  Put them into a collection somehow?  I'm not sure yet how that would work.

[ZePowerDiver]

Have you considered using a gallery and a edit/new form linked to it? The gallery being used for navigating through your list and the form for data entry/modification, this way it is submitted at each line you modify or add.

[lumberjacklurch]

The gallery is part of a form.  

The scenario I'm working for is this, I have a group that needs to compose a list of items to sell.  The form collects the department name, location, accounting info, and a list (the gallery) of items for sale from that department.  Since the list of items can be more than one row, I have to concat the gallery entries and save them to a multi-line text field.  As I said before, this works great if all users are doing is data entry.  But when they open an item in the list, I want the multi-line text field to show up and display the list of items for sale that were entered when the record was created.  I don't even need to be editable, they just need to display.  I've gotten it to the point where I can split the multi-line text by carriage return, and put the results into a collection, but I'm not sure how to proceed from there.  I thought about using Left/Mid/Right to parse out the values for the gallery, but those require Text input, and Split/a collection is a Table input.  I tried using ForAll, but that only lets you use Patch and Collect.  To use those I would still need to use Left/Mid/Right but I still run into the input type problem.  I'm racking my brain trying to figure this out, but I keep hitting walls.

[lumberjacklurch]

OK, I think I've made some progress.  After watching a couple of YouTube vids, I discovered the SharePointIntegration options.  More specifically, the On View option.  Now, it still doesn't work, but I got this in the On View option without any errors:

ClearCollect(col_ItemDetailView,
AddColumns(Split(ItemDetailValue.Text, Char(13)),
"Item", Left(Result,Find("|", Result)-1),
"Description", Mid(Result,Find("|", Result)+1, Find("|", Mid(Result,Find("|", Result)+1))-1),
"Quantity", Mid(Mid(Result,Find("|",Result)+1),Find("|", Mid(Result,Find("|", Result)+1))+1))

Like I said, it doesn't display anything, but I feel it's a step in the right direction.

[ZePowerDiver]

Have you looked at the results in the collection (namely col_ItemDetailView) to see if values are populated or not ?

[ChadVKealey]

@Mr-Dang-MSFT , wondering if you could weigh in on this? Basically, the "new item" form uses a gallery with text input fields to replicate InfoPath's "repeating table" control (a la Shane Young's method) but then concatenates the data into a single multi-line text field. So...

Qty     Item        Description

2         Laptops   Dead old laptops, good for parts

3         Tablets    Functional Android tablets

is concatenated into/becomes (again, in one multi-line text field)...

2 | Laptops | Dead old laptops, good for parts <line break>

3 | Tablets | Functional Android tablets

What we need to do is, when an existing item in the SP list is Edited, split that data back out into its components (in a collection) so that the same (or a similar) gallery/text field arrangement can be used to update the data. Actually, this might be a good candidate for a Component (if they can be connected to a Collection?).

In pseudo-code, what I think needs to happen is:

Split-Into-Columns(Split-Into-Rows(ML-Text-Field,<row-delimiter>),<column-delimiter>)

with one or more "ApplyToAlls" or "ForEaches" thrown in there. 

[lumberjacklurch]

@ZePowerDiver It's not, which is discouraging.  I don't think my syntax is wrong.  I get no errors.  I assume the collection is empty because when I'm editing the customized form, I am not simultaneously opening an item from the list.  So, since no item was selected, there is not a value for the ItemDetailValue field.

[lumberjacklurch]

In addition, it's unfortunate that (so far) there is no way to emulate the user behavior for a customized form.  Meaning, there's no way, while you are editing a customized form in PowerApps, to mimic a user clicking on an item to view it or clicking on the Edit button when they have an item open.  It would definitely be helpful in this situation.

[Mr-Dang-MSFT]

---

@ChadVKealey wrote:

Qty     Item        Description

2         Laptops   Dead old laptops, good for parts

3         Tablets    Functional Android tablets

is concatenated into/becomes (again, in one multi-line text field)...

2 | Laptops | Dead old laptops, good for parts <line break>

3 | Tablets | Functional Android tablets

In pseudo-code, what I think needs to happen is:

Split-Into-Columns(Split-Into-Rows(ML-Text-Field,<row-delimiter>),<column-delimiter>)

---

If I'm understanding, you write a pseudo table into a single field. One thing I'd caution here is that you may encounter caps on how many characters you can have in a single field. For instance, SQL has 4000 max characters.

That said, you'd need an action that upon selecting a record to edit, you'd do something like this:

// Set the selected record.
Set(selectedRecord,ThisItem);

Place the form in the right mode
EditForm(Form1);

ForAll(
    // Split the entries by line break
    RenameColumns(Split(ThisItem.Column,Char(10)),"Result","TextInEachLine"), 
    Collect(repeatingSection,
        {
            // Split up the text in a line by the pipe. Get the nth result to represent each column.
            Qty: Last(FirstN(Split(TextInEachLine,"|"),1)).Result,
            Item: Last(FirstN(Split(TextInEachLine,"|"),2)).Result,
            Description: Last(FirstN(Split(TextInEachLine,"|"),3)).Result
        }
    )
)

So I'm splitting each line of text in the multi-line text field. Char(10) is a line break. If it's not recognized, there's a few other methods to try. MatchAll is more powerful and may be needed depending on how you do linebreaks.

Then I split the text in each line by the pipes to determine the content for each column I actually want to collect.

Split creates a table of the things separated by pipes. FirstN gets n rows, but you want only the last one in that subset, so you use Last. This is how to get the nth row. It's a nice pattern because the structure is very similar, you just change 1, 2, 3... n for the order of your columns.

Let me know if you need further guidance on this.

[lumberjacklurch]

Mr. Dang,

Thank you very much for the reply.  
How would I get this to work in a customized form?  Where would I put this code so when a user clicks on an item in the SPO list, they can view the split text in a gallery?