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

Error creating multiple entries in CDS tables with lookup values

Hi everyone,

 

I'm having problems creating multiple entries in a CDS database withing a teams powerapp. I have a table tbl_BudgetDetails and another table tbl_Monthlies where the Column BudgetID references the primary Key of BudgetDetails.

 

On button press a new entry should be created in tbl_BudgetDetails and multiple entries in tbl_Monthlies with reference to my newly created BudgetDetails

My BudgetDetail entry is created fine and i can create multiple entries in tbl_Monthlies but unfortunately I cant get my reference working. My current code looks like this:

 

ClearCollect(ResultTable,
Patch(tbl_BudgetDetails,Defaults(tbl_BudgetDetails),{Budget_Title:txt_Title.Value,Startdate:DateValue(var_StartDate.Text)}));
ForAll(gal_MonthInput.AllItems,
Patch(
    tbl_Monthlies,
    Defaults(tbl_Monthlies),{
        MonthName:lbl_MonthName.Text,
        BudgetID: LookUp(Choices(tbl_BudgetDetails.BudgetID), BudgetID = First(ResultTable.BudgetID)),
        'Value per Month':Value(txt_BudgetMonth.Text),
        Owner: First(ResultTable.Owner).Owner
        }
    ))

 

My current error message is:

Invalid argument type. Expecting a Record value, but of different schema.
Missing column. Your formula is missing a column 'Owner' with a type of 'Polymorphic'

 

I added the "Owner:" line just for testing purpose. Removing this line creates the same error message.

If I remove the line creating BudgetID, everything is working with or without that owner line, so I think it is related to just this line. If I change the line to:

BudgetID: LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable.BudgetID)),

 I get an error message:

Invalid argument type. Expecting a Record value, but of different schema.

Incompatible type. The 'Status' (cr_23d_status) column in the data source you'updating expect a 'OptionSetValue' type and you're using a 'OptionSetValue' type.

I found a topic with that error message where it was suggested to include choices to get the right schema or ignore the error as it is a temporally problem. Unfortunately that is not the case for me.

 

If I test the LookUp formula in another control it displays the correct record / record.ID

 

I dont know if it matters, but I am using PowerApps Preview for Teams

 

thank you for your help!

8 REPLIES 8
summitb
Power Participant
Power Participant

Can you try BudgetID: LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable.BudgetID)).BudgetDetailsID

 

Passing the GUID should fix this!

Nilsnutz
Frequent Visitor

Unfortunately that is not working. I double checked that the lookup code is correct by using it in a different control:

LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable.BudgetID).BudgetID)

This code gets the correct Record from tbl_BudgetDetails.

 

Screenshot 2020-10-26 073644.png

 

If I use either:

BudgetID: LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable.BudgetID).BudgetDetailsID).tbl_BudgetDetail,

BudgetID: LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable.BudgetID).BudgetDetailsID).BudgetID,

 

I get type mismatch errors.

Screenshot 2020-10-26 074149.png

 

I really hope there is some help on this topic

Hi @Nilsnutz ,

 

Could you please share more details about your scenario? Do you want to use a button to submit multiple records to CDS entity with a LookUp field relates to another entity?

 

If you do, there are several points you should understand, unlike in relational databases:

  1. LookUp field: Entity relationship of 1:N in CDS means one entity relates to another therefore a lookup field in child entity contains the metadata of parent entity. Please refer to official doc: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-edit-1n-relationships
  2. You are not creating an ParentEntity.ID in child entity lookup field, but creating a relationship.
  3. You could display any fields of parent entity in child entity with a formula like ChildEntity.ParentEntity.FieldName, including ID.

 

So, the formula in your post patches records to tbl_Monthlies with the BudgetID which I assuming is a LookUp field should with a record of tbl_BudgetDetails but not the ID.

 

In summary, the formula should be modified to:

 

ClearCollect(ResultTable,
Patch(tbl_BudgetDetails,Defaults(tbl_BudgetDetails),{Budget_Title:txt_Title.Value,Startdate:DateValue(var_StartDate.Text)}));
ForAll(gal_MonthInput.AllItems,
Patch(
    tbl_Monthlies,
    Defaults(tbl_Monthlies),{
        MonthName:lbl_MonthName.Text,
        BudgetID:LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable).BudgetID),
        'Value per Month':Value(txt_BudgetMonth.Text),
        }
    )
)

 

BudgetID:LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable).BudgetID), this returns a record. So the mistake in your formula is the position of the closing parenthesis.

 

Hope this helps.

 

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Thank you for your help @v-jefferni but unfortunately your modified code doesnt work either

 

To clear things up a bit: I would like to create a new entry in a CDS table (tbl_BudgetDetails) and then create multiple entries in tbl_monthlies that has a lookup column called BudgetID that references tbl_BudgetDetails. I am using the new Dataflex for teams environment, where you can only create tables, but not entities, but I think it will remain the same.

 

On the first part of my Button onselect the parent element is created and the result stored in my local variable? ResultTable. I tried the following formula in a combo box to get the correct, newly created, record in my parent table:

LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable).BudgetID)

I can then output all

 

However, if I trie to use either this combobox.selected in my Patch function:

BudgetID:ComboBox1.Selected,

or use that LookUp directly

BudgetID:LookUp(tbl_BudgetDetails, BudgetID = First(ResultTable).BudgetID),

 

I'm keep getting errormessages that some columns in my data source are expecting a 'OptionSetValue' type and I am using 'OptionSetValue'.

 

If i preview my combobox Record, I do get a bunch of errormessages for some properties

Screenshot 2020-10-26 115307.png

 

As you mentioned that all my parent data is also stored in my child entity/table entry could it be that my parent record is missing something? The errormessage state that my data source has the problems, however I do not want to update the data source but use that data source record and store it in the child table.

I did some additional testing. If i run

ForAll(gal_MonthInput.AllItems,
Patch(
    tbl_Monthlies,
    Defaults(tbl_Monthlies),{
        cr52c_monthname:lbl_MonthName.Text,
        BudgetID:Last(Choices([@tbl_Monthlies].BudgetID)),
        cr52c_valuepermonth:Value(txt_BudgetMonth.Text)}
    ))
)

It will create the multiple entries in my child table. So I think I was wrong to get the correct data on the parent table but have to get the right choice option in my child table. Maybe someone can give me a hint on how this may help find a solution

summitb
Power Participant
Power Participant

If you want to get what you select from the checkbox, may be you can try this

 

BudgetID:LookUp(tbl_BudgetDetails, BudgetID = ComboBox1.Selected.BudgetID)

Hi @Nilsnutz ,

 

The sentence BudgetID:Last(Choices([@tbl_Monthlies].BudgetID)) just return a record which is the last BudgetID of the table tbl_Monthlies BudgetID column, it would not relate to BudgetDetails table.

 

I think there may be something special settings in those two entities? Are there any required fields need to be filled in?

I made a similar test with 2 entities in CDS and with formulas basically same to yours but worked fine with no error:

 

ClearCollect(ResultTable,
Patch('JefferNi Test Entity',Defaults('JefferNi Test Entity'),{JefferNiTestEntity2:DataCardValue7.Text}));
ForAll(Gallery4.AllItems,
Patch(
    JefferTestEntity2S,
    Defaults(JefferTestEntity2S),{
        Name: ThisRecord.Name,
        LookUps:LookUp('JefferNi Test Entity',JefferNiTestEntity2= First(ResultTable).JefferNiTestEntity2)
        }
    )
)

 

Or you may try to use another column to lookup like :

BudgetID: LookUp(tbl_BudgetDetails, Name = First(ResultTable).Name))       //Name represents another column of tbl_BudgetDetails entity

 

Please have a try.

 

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

I tried all solutions and unfortunately none of them worked in my patch formula. I have no problems getting the correct record and it works just fine when I display it on a DataTable. Its just not working as a reference in my patch formula. I tried the same thing on some other tables and I need to use choices here as well to create an entry.

 

Maybe this is something special using tables instead of entities with Teams and Dataflex. The only way to get it to work is using the last entry in the Lookup field in my tbl_Monthlies table. As this column is the reference to my BudgetDetails table and the last entry is the one I just added, I will use this until some other errors occur.

This is the only way my app creates entries in the tbl_monthlies table and puts the right record in the BudgetID column:

BudgetID:Last(Choices([@tbl_Monthlies].BudgetID)),

 

In my BudgetDetails table there are in fact some required fields, however these are internal fields like statuscode or modified. I think I will leave it with that code for now. I hope I wont have issues if I need to update those records in one of my next steps.

 

thanks for your support!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (72,665)