Power Platform Community Forum Thread Details

Really you want invoice numbers to be unique and shouldn't really have two invoices of the same number I have created an invoicing system similar to what you are requesting that uses dropdowns link to client accounts, separate invoicing accounts, contacts and properties etc, the system also works out CIS rates (UK), Tax, ledger accounts, quantities, discounts etc.

It will take a bit of work to get it up and running properly but the code I have is like:

the code on add row is

/////////////////////Collect Invoices Line Items/////////////////////
Collect(
InvoiceRowsCollection,
{
ColDescription: TextInput1_3.Text,
ColLedger: Dropdown1_2.SelectedText,
ColUnits: TextInput2_6.Text,
ColTotalAmount: TextInput3_3.Text,
ColDiscount: TextInput3_4.Text
}
);

/////////////////////Set Amount Variables and Strip Formatting/////////////////////

Set(RawVar, Substitute( TextInput3_3.Text, "£", "" ));
Set(RawVar2, Substitute( RawVar, ",", "" ));

/////////////////////Set Discount Variables and strip formatting/////////////////////

Set(RawDiscountVar, Substitute( TextInput3_4.Text, "£", "" ));
Set(RawDiscountVar2, Substitute( RawDiscountVar, ",", "" ));

/////////////////////Set Total Value of Line Items/////////////////////

Set(VarTotalValue, VarTotalValue + Value(RawVar2));

/////////////////////Set Sales Tax for Line Items/////////////////////

Set(VarGST, VarGST + Value(RawVar2)*.2);

/////////////////////Set Discount Amount for Line Items/////////////////////

Set(varDiscount, varDiscount + Value(RawDiscountVar2));

/////////////////////Set Discount Sales Tax for Line Items /////////////////////

Set(VarDiscountGST, VarDiscountGST + Value(RawDiscountVar2)*.2);

/////////////////////Set Discount Sales Tax for Line Items /////////////////////

Set(varSubTotal, DataCardValue12_2.Text);

/////////////////////Set Sales Tax Amount for CIs Labour /////////////////////

If(Dropdown1_2.Selected.Ledger = "CIS Labour", Set(VarCISLRGST, VarCISLRGST + Value(RawVar2)*.2));

/////////////////////Set Sales Tax Amount for CIS Mterials/////////////////////

If(Dropdown1_2.Selected.Ledger = "CIS Materials", Set(VarCISMLRGST, VarCISMLRGST + Value(RawVar2)*.2));

/////////////////////Reset Inputs/////////////////////
Reset(TextInput1_3);
Reset(TextInput2_6);
Reset(TextInput3_3);
Reset(TextInput3_4);
Reset(Dropdown1_2);

/////////////////////Set Formatted Numbers for Amount / Discounts////////////
Set(FormattedNum, "");
Set(FormattedDiscountNum, "");

the code on generate

Patch(
Invoices,
Defaults(Invoices),
{
Client: LookUp(
Accounts,
'🏢 Account Name' = DataCardValue2_1.Selected.'🏢 Account Name'
),
'Invoicing Account ': LookUp(
Contacts,
'📧 Email' = DataCardValue9_1.Selected.'📧 Email'
),
Property: LookUp(
properties,
'Full Address' = DataCardValue10_2.Selected.'Full Address'
),
Contact: LookUp(
Contacts,
'👤 Full Name' = DataCardValue8_2.Selected.'👤 Full Name'
),
WO: LookUp(
Projects_2,
'🛠️ Project Name' = Dropdown2.Selected.'🛠️ Project Name'
),
'Due Date': DateValue1_2.SelectedDate,
'Date Sent': DatePicker1_2.SelectedDate,
'Invoice Number': DataCardValue1_2.Text,
'Is CIS?': DataCardValue15_2.Selected.Value,
'CIS Deduction': Value(DataCardValue13_4.Text),
Shipping: Value(TextInput4_2.Text),
SubTotal: Value(DataCardValue12_2.Text),
'Discount Amount': Value(DataCardValue3_3.Text),
'Amount Due': Value(DataCardValue3_2.Text),
Tax: Value(DataCardValue12_3.Text),
'Additional Information': TextInput1.Text,
'Invoice Date': DatePicker1_2.SelectedDate
}
);
ForAll(
InvoiceRowsCollection,
Patch(
'Invoice Tasks',
Defaults('Invoice Tasks'),
{
Name: "Invoice Line Item" & DataCardValue1_2.Text,
Description: ColDescription,
Units: Value(ColUnits),
'Total Amount': ColTotalAmount,
'Invoice Number': DataCardValue1_2.Text,
'Invoice Header': Label4_1.Text,
'Ledger Account': TextInput2_5.Text,
'CIS Deduction': Value(DataCardValue13_4.Text)

}
)
);
Notify(
"Your invoice was successfully submitted",
NotificationType.Success,
5000
);
Clear(InvoiceRowsCollection);
Set(
VarTotalValue,
0
);
Set(
VarGST,
0
);
Set(
VarShippingGST,
0
);
Set(
VarCISLRGST,
0
);
Set(
VarCISMLRGST,
0
);
Set(
RawShippingVar2,
Blank()
);
///////////////////////Collect Invoice Numbers ////////////////////////
//////////////////////////////////////////////////////////////////////

ClearCollect(collectedInvoices, Invoices);
ClearCollect(collectedInvoiceNumbers, Distinct(collectedInvoices, 'Invoice Number'));

///////////////////////Set the invoicenum variable as next consecutive invoice number ////////////////////////
//////////////////////////////////////////////////////////////////////

Set(InvoiceNum, Text(Last(Sort(collectedInvoiceNumbers, Value)).Value + 1, "00000"));

Reset(TextInput4_2);
Reset(DataCardValue3_3);
Reset(DataCardValue2_1);
Reset(DataCardValue9_1);
Reset(DataCardValue10_2);
Reset(DataCardValue8_2);
Reset(TextInput1);
Navigate(Menu);

app on start

///////////////////////Collect Invoice Numbers ////////////////////////
//////////////////////////////////////////////////////////////////////

ClearCollect(collectedInvoices, Invoices);
ClearCollect(collectedInvoicesNumbers, Distinct(collectedInvoices, 'Invoice Number'));

///////////////////////Set the invoicenum variable as next consecutive invoice number ////////////////////////
//////////////////////////////////////////////////////////////////////

Set(InvoiceNum, Text(Last(Sort(collectedInvoiceNumbers, Value)).Value + 1, "00000"));

///////////////////////Create Collection Ledger Acccounts ////////////////////////
//////////////////////////////////////////////////////////////////////

ClearCollect(
LedgerAccounts,
{Number:1, Ledger: "Sales/Services"},
{Number:2, Ledger: "Sales/Materials"}
);
ClearCollect(
CISLedgerAccounts,
{Number:3, Ledger: "CIS Labour"},
{Number:4, Ledger: "CIS Materials"}
);

///////////////////////Reset Variables ////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////Invoices
Set(varDiscount, 0);
Set(VarDiscountGST,0);
Set(VarTotalValue, 0);
Set(VarShippingValue, 0);
Set(VarGST, 0);
Set(VarShippingGST, 0);
Set(varSubTotal, Blank());
/////////////////////////////////////////Quotes
Set(varQuotesDiscount, 0);
Set(VarQuotesDiscountGST,0);
Set(VarQuotesTotalValue, 0);
Set(VarQuotesShippingValue, 0);
Set(VarQuotesGST, 0);
Set(VarQuotesShippingGST, 0);
Set(varQuotesSubTotal, Blank());
/////////////////////////////////////////CIS
Set(VarCISLRGST, 0);
Set(VarCISMLRGST, 0);