Create record in collection for each record from a datasource table

[Amar_Singh]

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

[Drrickryp]

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. 

[Amar_Singh]

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

[Drrickryp]

@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.

[Amar_Singh]

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:

OrderID	Description

-QAOrderLines with no data at this point:

Line number	Test	OrderID	Result

-QA Tests with pre-populated data:

Test	Type
Contaminants?	Yes/No
Barcode validation	Barcode
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 number	Test	OrderID	Result
1	Contaminants?	001
2	Barcode validation	001
3	Damaged?	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

[Amar_Singh]

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):

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:

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