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

Binding Relationships Fields in One TO many relationships during data entry in a form

I am having a problem creating a report no. in a data entry form on the fly. The Report No. is Auto Generate, so the record has to be posted to create the number. However in the form below if SubmitForm after the Division name is selected (OnChange) the focus of the form is lost. Is there some way to post the record, get the Report No. and continue editing the Form.

Below is the CDS data structure:

Data Structure.JPG

 

I want to create the following data entry form:

Daily Report.png

 

Filling in the DIvision Name is not a problem, but during edit I have to generate the "Report No." (Does not gererate until post) for Daily Reports, Then use that entry in each Revenue entry. I am not thinking this should be so difficult.

 

Regards,

JasonF

1 ACCEPTED SOLUTION

Accepted Solutions
JasonF
Level: Powered On

Re: Binding Relationships Fields in One TO many relationships during data entry in a form

Thanks v-siky-msft,

This is a great solution and has taught me something valuable. I never thought of setting variables and definitely have use for this in other parts of my apps. However, last night I figured an even simpler solution to this problem. Let me know if you see a problem with this logic.

Since the Report No. is auto increment and is sequential I can easily return to a new record by going to the last record in the table. So I set the Items in the form to this:

Solution1.JPG

This forces the form to return the last record generated. Then I set the OnChange of the Division Name to SubmitForm(Form2):

solution2.JPG

 

The "New Form" button or I guess we will call it "New Report" invokes the NewForm(Form2) function. this in turn triggers the Onchange event, which posts a record, the returns to that record via items on the form to new record and now I have the ReportNo. Do you see any flaws in this logic?

 

Regards

 

View solution in original post

5 REPLIES 5
v-siky-msft
Level 10

Re: Binding Relationships Fields in One TO many relationships during data entry in a form

Hi @JasonF ,

 

Can you share more details about components of data entry form? 

 

The following is my thought, hope to help.

What i think is that the data entry form should contain two part, the one is Daily report Edit Form, another one is Revenue Form.

Once the Report NO. is generated automaticlly, continue to enter amount of Revenue EditForm. After each Revenue entry is entered, saved Type, Amount and Report No. to the new record.

Patch(Revenue, Defaults(Revenue),{'Revenue Type': TextInput1.Text,'Sales':TextInput2.Text,'Report NO.': Last('Daily Report')})

If any better idea, please post back.

Best regards,

Sik

 

JasonF
Level: Powered On

Re: Binding Relationships Fields in One TO many relationships during data entry in a form

This what I was thinking for the details of Revenue. However I am still struggling with how to get the "Report No." that is autogenerated in the "Daily Report" CDS. The previous attached view of the form was mockup from a requirements doc. Attached below would be more like how the it would appear in PowerApps the top part a form and the bottom a gallery: 

Note: PID in this example would be what I was referring to as "Report No". I need to get the PID (Report No.) in the top form before apply your solution to the bottom Gallery (Revenue). If I Submitform the top record, it is lost I still do no know what the PID would be. Is there a way to post the record for the top form and not loose focus of the record.

Daily Form.JPG

v-siky-msft
Level 10

Re: Binding Relationships Fields in One TO many relationships during data entry in a form

Hi @JasonF ,

 

Try to pass the selected Division Name to Variable before submitting form. Datacardvalue1 is the name of DivisionName combo box, and 'Division Name' is the field name of 'Division' that combo box lists.

Set(Var,DataCardValue1.Selected.'Division Name');SubmitForm(Form1)

Use lookup function to match the record that 'Division Name' was selected just now, set Items property of form as below:

LookUp(Accounts,'Division Name'.'Division Name' = Var) / *first Division name is lookup field name of Daily report, the second is field name of Division*/ 

So the ultimate idea: 

We need "New" button to perform NewForm function, when the form mode is new, we select the division name, and click submit button to submit the form and get the PID, in addition the form mode turns to be Edit, then the form would show the match record which contains the automatically generated PID. That is what you want.

Annotation 2019-10-23 100813.pngselect combo box and submit formAnnotation 2019-10-23 100859.pngForm dispalys the match record containing auto-no

Best regards,

Sik

 

JasonF
Level: Powered On

Re: Binding Relationships Fields in One TO many relationships during data entry in a form

Thanks v-siky-msft,

This is a great solution and has taught me something valuable. I never thought of setting variables and definitely have use for this in other parts of my apps. However, last night I figured an even simpler solution to this problem. Let me know if you see a problem with this logic.

Since the Report No. is auto increment and is sequential I can easily return to a new record by going to the last record in the table. So I set the Items in the form to this:

Solution1.JPG

This forces the form to return the last record generated. Then I set the OnChange of the Division Name to SubmitForm(Form2):

solution2.JPG

 

The "New Form" button or I guess we will call it "New Report" invokes the NewForm(Form2) function. this in turn triggers the Onchange event, which posts a record, the returns to that record via items on the form to new record and now I have the ReportNo. Do you see any flaws in this logic?

 

Regards

 

View solution in original post

v-siky-msft
Level 10

Re: Binding Relationships Fields in One TO many relationships during data entry in a form

Hi @JasonF ,

 

This is my first thought, actually, this is more convenient.

However, during my test, I found the record i just create may not be the last record everytime. So I suggest you to use LookUp function to find out the match record which is the most reliable.

 

Best regards,

Sik

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 176 members 5,996 guests
Please welcome our newest community members: