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

SharePoint list and form design

Hi experts,

I have a question it is more like share point list designing issue.

I am trying to build an APP to show me how my company uses the credit card, I am using SharePoint as data sourse.

sometimes the same invoice that I want to attach may contain purchase related to many programs (departments)

is there any suggestion how save the form using many programs code that requests to divide the money amount too. (One to Many )

I'll attach an image to show you what I mean exactly.

Thanks in advance

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
jlindstrom
Super User
Super User

I would create an invoice list and an invoice line list. On the line put the program code (which could be a lookup to a program code list. Relate invoice lines to invoice with a lookup type field.

 

In your app you could build a screen that has the invoice header details and a gallery of related invoice lines. Set items of gallery to something like Filter(invoicelines, invoicelines.invoice.Id=Invoice.ID.

 

@MalekA If this answers your question please mark as solution

View solution in original post

v-xida-msft
Community Support
Community Support

Hi @MalekA ,

Do you want to attach a invoice with multiple programs code (Departments)?

 

Based on the needs that you mentioned, I agree with @jlindstrom 's thought almost. You could consider create another individual SP List (called "Program Codes") to store the programs codes (using "Program Code" column, Text column) and corresponding amount value (using "Amount" column, Number column).

 

Then within your CC Usage SP List, you should configure the "Program Code" column as a LookUp field type column to reference values from the "Program Code" column in your "Program Codes" List, and enable "Allow Multiple Selections" option for this LookUp field (so that you could selected multiple options within the "Program Code" LookUp column). The Amount field value should be calculated automatically based on the selected options within the "Program Code" LookUp field.

You need to re-generate a new canvas app based on your SP List, then go to the Edit form, set the Default property of the Amount Text Box to following:

If(
    EditForm1.Mode = FormMode.New,
    Sum(
        ForAll(
               ProgramCodeComboBox.SelectedItems,
               LookUp('Program Codes List', 'Program Code' = Value, Amount)  // find corresponding amount value for each selected program code in your 'Program Codes' List
        ),
        Value
    ),
    Parent.Default
)

You could select multiple options within the Program Code ComboBox, then corresponding total Amount value of these selected Program Code options would be calculated within the Amount field data card automatically.

 

Please take a try with above solution, hopes it could help in your scenario.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
jlindstrom
Super User
Super User

I would create an invoice list and an invoice line list. On the line put the program code (which could be a lookup to a program code list. Relate invoice lines to invoice with a lookup type field.

 

In your app you could build a screen that has the invoice header details and a gallery of related invoice lines. Set items of gallery to something like Filter(invoicelines, invoicelines.invoice.Id=Invoice.ID.

 

@MalekA If this answers your question please mark as solution

View solution in original post

v-xida-msft
Community Support
Community Support

Hi @MalekA ,

Do you want to attach a invoice with multiple programs code (Departments)?

 

Based on the needs that you mentioned, I agree with @jlindstrom 's thought almost. You could consider create another individual SP List (called "Program Codes") to store the programs codes (using "Program Code" column, Text column) and corresponding amount value (using "Amount" column, Number column).

 

Then within your CC Usage SP List, you should configure the "Program Code" column as a LookUp field type column to reference values from the "Program Code" column in your "Program Codes" List, and enable "Allow Multiple Selections" option for this LookUp field (so that you could selected multiple options within the "Program Code" LookUp column). The Amount field value should be calculated automatically based on the selected options within the "Program Code" LookUp field.

You need to re-generate a new canvas app based on your SP List, then go to the Edit form, set the Default property of the Amount Text Box to following:

If(
    EditForm1.Mode = FormMode.New,
    Sum(
        ForAll(
               ProgramCodeComboBox.SelectedItems,
               LookUp('Program Codes List', 'Program Code' = Value, Amount)  // find corresponding amount value for each selected program code in your 'Program Codes' List
        ),
        Value
    ),
    Parent.Default
)

You could select multiple options within the Program Code ComboBox, then corresponding total Amount value of these selected Program Code options would be calculated within the Amount field data card automatically.

 

Please take a try with above solution, hopes it could help in your scenario.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

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

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (28,647)