purchase order/invoice system...budget

[e3digital]

Hi Guys!

I need to keep track of my budget as I'm buying supplies for any of the 3 media centers I'm in charge of. (They each have their own budget.)

I have files Requisition.fp5, Line Items.fp5, and Vendors.fp5 which all work to create my requistions. I want to add Budgets.fp5 so that when I'm creating a requisition, I can see on-the-fly where my budget is at.

I want to always know what my beginning balance was, as well as the remaining balance after each purchase. So it would look like this:

Beginning Balance: $1000.

Order #1: $50

Remaining Balance: $950

Beginning Balance: $1000

Current Balance: $950

Order #2: $100

Remaining Balance: $850

Beginning Balance: $1000

Current Balance: $850

Order #3: $250

Remaining Balance: $600

I suspect it involves a global field and copying/pasting, but I can't quite get my head around it...

Thanks for any help,

Susan

[Jim McKee]

Hi Susan ...

Assumptions:

1. You want to be able to view the "Beginning Balance", "Current Balance" and "Remaining Balance" from Budgets while you are creating a Requisition.

2. As you add items to a Requisition, the "Remaining Balance" should reflect the result of newly added items.

3. When the Requisition is completed, the display of these values is unnecessary.

Here is how I'd approach it:

Create a relationship between the budgetID field in Requisitions with the budgetID field in Budgets. Create this same relationship in Budgets back to Requisitions.

I assume you already have a calculation field in Requisitions that sums the line item amounts for each requisition -- something like Sum(reqID | LineItems_reqID::lineitemamount). Let's call it "c_Requisition Total"

Create a field in Requisitions (let's call it "b_processed"). This will be a number field linked to a ValueList containing only the value 1. Put the field on your Requisition entry layout and make it a checkbox.

Create a calculation field in Requisitions. Call it "processed_ Requisition Total":

Case(IsEmpty(b_processed), 0, c_Requisition Total

This will output the "c_Requisition Total" result only if "b_processed" is checked.

Make your "Current Balance" field in Budgets a calculation:

= Beginning Balance - Sum(budgetID | Requisitions_budgetID::processed_Requisition Total)

This will be the Beginning Balance minus the sum of all related Requisition Totals that have been marked as "processed" (b_processed = 1).

Make your "Remaining Balance" field in Budgets a calculation:

= Beginning Balance - Sum(budgetID | Requisitions_budgetID::c_Requisition Total

This will calculate the Beginning Balance minus the sum of all related Requisition Totals, including the Requisition you're currently creating.

Now create once more calculation field in Requisitions. Let's call it "processed_budgetID":

= Case(IsEmpty(b_processed), budgetID, "")

This will output the budgetID of the current record if "b_processed" is NOT checked.

Create one more relationship from Requirements to Budgets. Lets call it "processed_budgetID match". On the left side of the pair select the "processed_budgetID" calculation. On the right side, select the budgetID field in Budgets.

One at a time, drag three fields onto your entry layout in Requisitions. These will be based on the "processed_budgetID match" relationship above. Select the relationship in the pop-up at the top of the field selection window for each of the three fields, and select (one for each field) the "Beginning Balance", "Current Balance" and "Remaining Balance" fields in the list.

Now that you have your three balance fields on your layout, create a new Requisition record, enter some line items, and watch your "Remaining Balance" change. When you finish with the Requisition, check the "b_processed" field, and the balance fields will disappear. You can get fancy and create field labels that will also display and disappear with the balance fields.

Check the attached demo and post your questions here.

Good luck.

Budget Maintenance.zip

[Anatoli]

You will experience performance hit with more records, because the calculations are running across many records.

Depending on hardware and OS you'll be OK up to 20-50k records.

Better is usage of static numbers and script driven Transaction system. There are some posts about that. No problems with 2 million records in FM database.