cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
originalltd
Level: Powered On

Multi-line Text to Single SQL Lines

Hi,

 

What expression is used to seperate each line from a multiline input, to single line entries in SQL Server?

See below image.

 

For each "Serial Number" (3rd Box in Image) create a new SQL line with the "Batch code" and "Parent Code" for it.

What would the Microsoft Flow process be for splitting the serial numbers up?

 

Thanks!

 

Capture6.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Multi-line Text to Single SQL Lines

If this is what you want, you don't need to use Flow - PowerApps can do it by itself. If you use an expression like the one below, you will insert 3 records with the information that you want. You'll need to replace the SQL table name, and the actual values for the batch code and parent code, though.

ForAll(
    Split(
        Trim(TextInput1.Text),
        Char(10)),
    Patch(
        <your sql table name>,
        Defaults(<your sql table name>),
        {
SerialNumber: Result,
BatchCode: "the-batch-code",
ParentCode: "the-parent-code"
}
)
)
6 REPLIES 6
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Multi-line Text to Single SQL Lines

You can use the Split function to split the text in the multi-line text box in multiple lines. For example, this expression will return a table with the individuiual lines of the text box (Char(10) is the newline character):

Split(
    Trim(TextInput1.Text),
    Char(10))

What do you mean by "to single line entries in SQL server"? Do you want, at the push of a button, for example, to add one record from each of those lines? How would you calculate the 'batch code' and 'parent code' from those lines?

originalltd
Level: Powered On

Re: Multi-line Text to Single SQL Lines

@CarlosFigueira

 

Thank you for your reply.

 

Single Line Entries in SQL Server - 

By using Microsoft Flow, I'd like to create a new line per Serial number, with the same Batch codes and parent code. But I am not sure how to seperate the Serial Numbers (for each) in Flow - as it's a multi-line output. How would I go about splitting the lines up in Flow?

 

Thanks

 

 

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Multi-line Text to Single SQL Lines

You talked about using Microsoft Flow, but your image shows what looks like PowerApps, can you clarify what you want to accomplish?

 

The solution I sent to split the multi-line text input into multiple strings; what is also not clear is what the values for both batch code and parent codes should be. For example, if you have the following codes:

BLYNCUSB30
BLYNCUSB31
BLYNCUSB32

What would the values for the batch code / parent code columns be? The same (BLYNCUSB3x)? Something different? If those are the values for those new columns, then you can use some expression like the following:

ForAll(
    Split(
        Trim(TextInput1.Text),
        Char(10)),
    Patch(
        <your sql table name>,
        Defaults(<your sql table name>),
        { BatchCode: Result, ParentCode: Result }))
originalltd
Level: Powered On

Re: Multi-line Text to Single SQL Lines

@CarlosFigueira

 

That is correct, I am (attempting) to use both PowerApps and Microsoft Flow to achieve the following:

 

User completes the 3 Entry Fields in the photo in the first post.

For each Serial Number in the Multi-line (in this example, 3 serial numbers) I want 3 x Lines in my SQL Table.

 

In the table, 3 columns, Serial Number | Batch Code | Parent Code

 

The 3 x Serial Numbers have the same Batch codes and Parent Code. So each line should have the same Batch codes and parent code, but different serial numbers per line.

 

Thanks,

James

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Multi-line Text to Single SQL Lines

If this is what you want, you don't need to use Flow - PowerApps can do it by itself. If you use an expression like the one below, you will insert 3 records with the information that you want. You'll need to replace the SQL table name, and the actual values for the batch code and parent code, though.

ForAll(
    Split(
        Trim(TextInput1.Text),
        Char(10)),
    Patch(
        <your sql table name>,
        Defaults(<your sql table name>),
        {
SerialNumber: Result,
BatchCode: "the-batch-code",
ParentCode: "the-parent-code"
}
)
)
originalltd
Level: Powered On

Re: Multi-line Text to Single SQL Lines

@CarlosFigueira

 

Many thanks, that formula worked perfectly Smiley Happy

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 52 members 4,239 guests
Please welcome our newest community members: