I created an invoicing system, but when I try to create invoices in bulk, I have performance issues. I initially had four different actions grouped together, but that was causing the browser to generate a "This page isn't responding" when many records were selected. I then moved three of the actions into a bot. That eliminated the browser errors, but it still had dismal performance. I then though maybe the issue was that my members table has a lot of related tables and any updates to those records would e problematic. So I created a little table to store invoice requests in. But that was problematic because I forgot that I can't use Input() on a new row action.
As an experiment, I set the add row action to simply add a new row using one value from my members table and one uniqueid() calculation. Creating a hundred of these records took about 3 minutes, which makes me thing the whole idea of doing bulk actions to about 100 records is perhaps, a non-starter.
Anyways, if anyone has thoughts on how I might improve performance, here's the details for the original design:
I have tables for members, invoices and invoice items. I have an action that runs on the members table to invoice a member. The action is a grouped action that does four things:
- Creates a new uniqueid in a members column
- Creates a new invoice, using the uniqueid as the id for the invoice
- Creates a new invoice item, using the uniqueid to tie it to the invoice
- Updates the invoice status to "Ready for Sending"
Within the invoice item, I have the following calculated fields:
Amount Before Prorating
Max(List(
IF([use_flat_pricing] = True,
[flat_price],
[unit_price] * [quantity]),
[minimum_price]
))
Prorate Percentage:
IF(AND([Prorate] = True, today() > [start_date]),
1 - (HOUR([end_date] - TODAY()) / HOUR([end_date] - [start_date])),
0)
Amount:
[amount_before_prorating] * (1 - [pro-rate_percent])
Unit Price (applies pro-rating if selected):
IF([Prorate] = True,
(TotalHours([end_date]-[start_date])/24) /
(TotalHours([Product].[normal_end] - [Product].[normal_start]) / 24) * [Product].[price_per],
[Product].[price_per]
)
Quantity:
SWITCH([formula],
"Length * Rate + Beam * Rate", ([related_invoice].[boat].[length] + [related_invoice].[boat].[beam]) ,
"Length * Rate", [related_invoice].[boat].[length],
"Length * Beam * Rate", [related_invoice].[boat].[length] * [related_invoice].[boat].[beam] ,
"None", 1,
1
)
And in Invoices, I have the following calculated fields:
Pre-tax_total:
SUM(Select(invoice_items[amount],[related_invoice] = [_THISROW].[id]))
Invoice Counter:
Count(Select(Invoices2[id],
Year([create_date]) = Year(today())
)
)
+ 1
Any suggestions appreciated. I'll be building a web app version in the meantime. Cheers!
LinkedIn
Twitter
