Solved: Invoice calculations

Ramole · ‎04-13-2020

Hi

Can anyone help me sales calculations for example

Date Sku Description txtQty txtRate txtAmount

4/13/20 RB123 Coke 1.5 L 4 $1.50 $6

SharePoint List

txtQty - number field
txtRate - Currency field
txtAmount - Currency field

Thanks

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

v-yutliu-msft · ‎04-15-2020

Hi @Ramole ,

Have you tried my solution?
1)connect your app with product list and SalesTable list

2)set product combo box:

Choices(SalesTable.'Product/Service')

3)set the descrption textinput's default:

LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Value,description)

//you need to get desrption value from product list, so you need to filter productlist, not saleslist.

Best regards,

Community Support Team _ Phoebe Liu
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

eka24 · ‎04-15-2020

If you want to Patch, this is the documentation:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

Therefore you do something like:

If it a form:

Patch( SalesTable, Defaults( SalesTable),

{QuantityColumn: Value(txtQty.Text),

AmountCoulumn: Value(txtAmount.Text,

DescriptionColumn: DescColumn.Text})

If its a Collection:

ForAll(CollectioName, Patch(

SalesTable,

Defaults(SalesTable),

{QuantityColumn: Value(txtQty.Text),

AmountCoulumn: Value(txtAmount.Text,

DescriptionColumn: DescColumn.Text})

On the Multiselection , Allow multi selection as below:

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solu...

View solution in original post

v-yutliu-msft · ‎04-16-2020

Hi @Ramole ,

Q1:Do you want to save the description that you filter based on product to the description field(lookup type) in Salestable list?

If so, could you tell me whether do you want to use a form to update or patch function to update?

If you use a form to update, please set description datacard's Update:

{Id:LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Value,ID),
 Value:LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Value,description)
}

If you use patch to update, please use formula like this to update:

Patch(SalesTable,Defaults(SalesTable),
{Product/Service':ComboBox1.Selected,
description:
            {Id:LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Value,ID),
              Value:LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Value,description)
             }
}
)

//please replace with your column names.

Q2:

Do you want to display two columns in primary text, search for multiple field in combo box for product?

If so, since the product field and sku field is lookup field, they can not be directly display together in a combo box.

Try this:

set the combo box's Items:

AddColumns(SalesTable,"Product/Servicevalue",'Product/Service'.Value,"Product/Service:SKUvalue",'Product/Service:SKU'.Value)

set the combo box's layout to double, choose "Product/Servicevalue" and "Product/Service:SKUvalue" as primary text.

set the combo box's searchfield to :

["Product/Servicevalue","Product/Service:SKUvalue"]

Just like this:

//please notice that you can only get product and sku existing value in salestable list in this situation.

However, if you set the combo box's Items like this, the description textinput's default will need to be changed too.

Try this:

LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Product/Servicevalue,description)

Also, the formula about updating needs to be changed too.

Best regards,

Community Support Team _ Phoebe Liu
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

eka24 · ‎04-13-2020

Use:

txtRate.Text *txtQty.Text

OR

Value(txtRate.Text) *Value(txtQty.Text)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Ramole · ‎04-13-2020

Hi @eka24

sorry i forget to ask Product is Lookup field and description is on the product list same, how can i make it when i select product the description to come auto please, see the screenshot.

Date Product Description txtQty txtRate txtAmount

4/13/20 RB123 Coke 1.5 L 4 $1.50 $6

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

eka24 · ‎04-14-2020

Try the formula below in the Description textbox if you want autopopulate description:
LookUp(YourTableName,Product=ProductTextBoxText,Descripton)

If the product is a dropdown:

LookUp(YourTableName,Product=ProductDropdown.SelectedValue,Descripton)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Ramole · ‎04-14-2020

Hi @eka24

I dont know what wrong i ma doing but is not populating description from the comboBox to txtDescription

LookUp(SalesTable,'Product/Service'.Value=ComboBox1.Selected.Value,'Product/Service:Purchase Description'.Value)

And combo-box i am using this code on items property

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

v-yutliu-msft · ‎04-14-2020

Hi @Ramole ,

Do you mean that?

1) product field is text type in product list

2)description is text type in product list

3)product field is look up type in SalesTable(look up to product field in product list)

If so, please set txtDescription's Text:

LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Value,description)

//description is a field in product list, you need to filter based on product list

Best regards,

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

eka24 · ‎04-14-2020

Please which of your column names are choices in your sharepoint list

Is this 'Product/Service:Purchase Description' two columns or one

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Ramole · ‎04-14-2020

Hi @eka24

here is my SharePoint List columns

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

Ramole · ‎04-14-2020

Hi @v-yutliu-msft

Yes to filter so when i select product combo, the descriptions to auto populate on textbox

Thanks

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

v-yutliu-msft · ‎04-15-2020

Hi @Ramole ,

Have you tried my solution?
1)connect your app with product list and SalesTable list

2)set product combo box:

Choices(SalesTable.'Product/Service')

3)set the descrption textinput's default:

LookUp(Productlist ,'Product/Service'=ComboBox1.Selected.Value,description)

//you need to get desrption value from product list, so you need to filter productlist, not saleslist.

Best regards,

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

Invoice calculations

Helpful resources

Join us for Microsoft Power Platform Conference

June User Group Leader Call

Register for a Free Workshop

New Release Planning Portal (Preview)