cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create record in collection for each record from a datasource table

Hi all,

 

Okay so this is my first post on these forums so hopefully I can explain my issue properly.

I am trying to create a quality order like PowerApp similar to quality orders in D365FO.

 

I have the following tables in CDS:

 -QAOrder headers

 -QAOrder lines

 -QATests

 

In the PowerApp I would like to automatically create order line records when I create a new quality order header. I would like to create an order line per record in the "Tests" table.

E.g if the "Tests" table has the following records:

 -Visual check

 -Temperature check

 

Then I would want two order lines created per new header in the App.

 

The order lines are structured as follows: |Line number|Test|Order number|Results|

 

I essentially want it to create the following output when the order header is created:

1, Visual check, Order number (just created), Blank (will update later)

2, Temperate check , Order number (just created), Blank (will update later).

 

Further on from this I want to pull the data of the new lines back into the App for the user to be able to update the results of each line and then Patch the updated results back into the Order lines table but I havent got this far yet.

 

I am currently trialling ideas and have not got to the order number or the line number bits but would appreciate help with that too. For the moment we will stay on the tests bit and I have tried the following formula:

 

ForAll(QATests,Collect(testcollection, {LineNumber:???, test:QATests.Test, OrderNumber:???, Result:???}))

 

The question marks are because I havent got to those elements yet but I take them out for testing purposes.

I end up with each test record in the test field for that collection per record. I want the first collection record to take the first test record and the next collection record to take the next test record and so on.

 

Any ideas? Sorry if I havent explained this in the best way.

 

Thanks in advance

5 REPLIES 5
Highlighted
Super User III
Super User III

Hi @Amar_Singh 

If you want to see how to build a canvas app with CDS please see https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/northwind-install  for a great example that uses a number of related tables.  Once you have reviewed this information, if you are having a specific issue, please come back and post it. 

Highlighted

Hi @Drrickryp

 

Thanks for this information I will definitely take a closer look at this app. I guess for now the specific question is how do I create multiple records in a collection for all unique records from another datasource whilst using the first record value from a column in the first collection record and then using the second record value from the same column in the next collection record and so on.

 

Think this may have been lost in all of the detail above.

 

Thanks

Highlighted

@Amar_Singh 

It seems like you want to collect records grouped by one of the columns.  If so, check out https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby  for an example.  You can use more than one column to group your data.  So if you had a table like the one on the left, you could group them by city and population like in the right side one.Annotation 2020-06-01 155641.pngAnnotation 2020-06-01 155642.png

 

 

 
Highlighted

Hi @Drrickryp,

 

Nop sorry not quite that. Let me see if I can give a better example:

 

CDS Tables are:

-QAOrderHeader with no data at this point:

OrderIDDescription
  

 

-QAOrderLines with no data at this point:

Line numberTestOrderIDResult
    

 

-QA Tests with pre-populated data:

TestType
Contaminants?Yes/No
Barcode validationBarcode
Damaged?Yes/No

 

If I create a new record (e.g Order ID - 001) in the QAOrderHeader table (using a form in PowerApps), I want to automatically create new records in the QAOrderLines table for every record in the QA tests table using the "Test" field from "QATests" table as the "Test" field in the QAOrderLines table.

 

This would mean the following data is created in the QAOrderLines table:

Line numberTestOrderIDResult
1Contaminants?001 
2Barcode validation001 
3Damaged?001 

 

I have figured out the OrderID and number sequence for line number but what I cannot figure out is how to create a record per Test. I can get it to create the 3 records into a new collection but each record either uses the same test (if I use First or Last from QAtests) or tries to embed all 3 tests into each record in the collection.

 

I dont mind if this is achieved using a collection first and then patching or patching directly back to the QAorderlines table. This is because I just need the output of the created records to display on the next screen so I can update the results.

 

Hope this explains the problem a little better.

 

Thanks

Highlighted

Hi @Drrickryp

 

Not sure why my last reply got marked as Spam but I will attach some screenshots and code to show you where I have got to so far and what I am still trying to achieve. For the moment I will make the scenario a little easier and say that we need to create the QAOrderLines when selecting an existing QAOrderHeader record from a gallery rather than creating a new OrderHeader.

 

Screen 1 (List of existing order headers):

QAHeaders.PNG

When I press the next arrow on a record it takes me to Screen 2 where I have a collection based where records are created for all tests in the QAtests table:

QALines.PNG

As you can see I have achieved the Line number, OrderID and Result fields outputs that I needed. The only thing that isnt working is the test field. Each record should be the next test record from the QAtests table but instead it duplicates the first record. I am aware this is because I am using "First" function in my code currently but I have tried not using "First" and it puts all tests into each record.

 

Here is my code from selecting in Screen 1:

Select(Parent);
ForAll(
    QATests,
    Collect(
        testcollection,
        {
            Name: Gallery1.Selected.'Quality order',
            Test: First(QATests.Test),
            Result: "Blank"
        }
    )
);
ForAll(
    testcollection,
    Collect(
        rownumber,
        Last(
            FirstN(
                AddColumns(
                    testcollection,
                    "RowNumber",
                    CountRows(rownumber) + 1
                ),
                CountRows(rownumber) + 1
            )
        )
    )
);
Navigate(
    Screen2,
    None
)

 

Thanks

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (13,537)