Using calculated globals with relationships

[RWX]

I have a situation where I'd like to use a calculated global field. Although FileMaker will let me define the field, the calculation doesn't work. I'm still a bit unclear on when calculated globals may be used, so perhaps someone with a bit more experience can explain a few things...

I have a relationship defined that uses a global on one end. The user selects a record from a portal. That record's ID number is placed in a global and this creates a temporary relationship to another table. I'm using this for setting up a series of payment due dates for an item. The first portal shows all line items on an invoice. The user selects an item and its ID is placed in the temporary global field. This creates a relationship to another table that stores payment due dates. Through a set of scripts, the user may add a payment due date, alter an entry, etc.

What I'd like to be able to do is create a global calculated field that sums the payment amounts in the due dates table. Consider:

The user selects Product A from the invoice line items portal. A script inserts Product A's ID number into the temporary global field, creating a relationship from the Invoices table to the Due Dates table. I'd like to have a global calculation in the Invoice table defined as sum(Due Dates::Amount Due). Although I can set it up, the result is always $0.00. If I change the field definition to not be global, it works. Is it not possible to use a global here?

Thanks!

-Rob

[Søren Dyhr]

The user selects Product A from the invoice line items portal. A script inserts Product A's ID number into the temporary global field, creating a relationship from the Invoices table to the Due Dates table. I'd like to have a global calculation in the Invoice table defined as sum(Due Dates::Amount Due). Although I can set it up, the result is always $0.00. If I change the field definition to not be global, it works. Is it not possible to use a global here?

Come again, why do you need a global for the temporary field that stores which invoice line to point at. If you flip the rolodex to another invoice the plucked line belongs to another invoice???

--sd

[Barbecue]

Come again, why do you need a global for the temporary field that stores which invoice line to point at. If you flip the rolodex to another invoice the plucked line belongs to another invoice???

I think because the invoice ID is being pulled from a different portal on the same layout?

It seems to me you shouldn't use a global calculation here. A global field's value is the same regardless of what record you're on, so it doesn't really make sense in this context. I think you probably want a summary field or an unstored calculation instead, which will change its value depending on the current record, but doesn't take up space in the database.

[Søren Dyhr]

I think because the invoice ID is being pulled from a different portal on the same layout?

But isn't it the lines ID that are copied to a field to show payments details for a single line in the invoice??? I would agree that such a richness in detail is a little unusual ...but thats how I read the question in the first place???

--sd

[RWX]

Come again, why do you need a global for the temporary field that stores which invoice line to point at. If you flip the rolodex to another invoice the plucked line belongs to another invoice???

When the user sets up the payment "schedule" for an invoice, he is shown a screen with all items on the invoice in one portal. To set up a payment schedule for an item, he clicks a little button on the desired invoice line item portal row. This inserts the record ID for that line item into the global. A second portal now displays the related payment schedule records for this line item.

Unfortunately, payment schedules must be linked to line items, not the invoice. Everything would be A LOT easier if payments, credits, the payment schedule, etc. could all be linked to the invoice record, but everything must be entered at the line item level, per my client's requirements. So I need to show the invoice, but make it easy and logical for the user to associate a payment schedule entry with a line item, not the invoice itself.

My solution is the one I've described: create an invoice view with one portal displaying the line items and a second displaying the payment schedule for the selected line item. Since I know the line item cost, I wanted to use globals to calculate the difference in payment schedule vs. the cost of the item. All of this is temporary. This information is only use while setting up the schedule. Let's say the line item costs $500. The user selects the line item and the ID is placed in the temporary global. If he's creating the payment schedule, no records appear. So he clicks the "Add" button and is prompted to enter a date and amount due. He enters 12/1/2004 and $100. Here's where I wanted to use globals to calculate. Since this data is only relevant in the moment - for error-checking purposes - I wanted a global calculation to update saying that the total payment schedule amount entered thus far is $100 and the balance (to be scheduled) is $400. The user enters another payment...and the numbers update again. Etc.

One thing to note...this might sound a bit excessive, design-wise, but my client has been very clear about the need to plug any potential holes in data entry. So I'm left scripting a lot of things, using temporary fields to validate data before creating a record, etc. The user cannot, for example, enter data into a portal row anywhere in the system. If data entry is required, the user clicks a little button and a dialogue box or window is opened.

-Rob

[Søren Dyhr]

Let's say the line item costs $500. The user selects the line item and the ID is placed in the temporary global. If he's creating the payment schedule, no records appear. So he clicks the "Add" button and is prompted to enter a date and amount due. He enters 12/1/2004 and $100. Here's where I wanted to use globals to calculate.

Ok the thing you put in the temporary field is the lineID, but the cost does still belong to the lineitem table, eventhough you add lines in the payments portal, is the cost pretty static, and could be pulled to a position next to the portal, but actually yet another relation to lineItem table and not the payment table. There is no need for actual calculation or is there???

--sd

[RWX]

Ok the thing you put in the temporary field is the lineID, but the cost does still belong to the lineitem table, eventhough you add lines in the payments portal, is the cost pretty static, and could be pulled to a position next to the portal, but actually yet another relation to lineItem table and not the payment table. There is no need for actual calculation or is there???

I solved this problem by using a mix of global fields and unstored calculation fields. This layout is only used for entering or modifying the payment schedule and is associated with the Invoice table. The user is presented with a two portals. The upper portal shows all invoice line items. The lower portal shows the payment schedule records that are tied to a selected invoice line item. The user selects a line item and clicks a little button (in the upper portal).

A script grabs the LineItemID value and the FinalLineItemCost (class cost minus applicable discounts, etc) and places them in temporary globals in the Invoice table. One global stores the LineItemID and is used in a relationship to the Payment Schedule table. The second global stores the cost. The unstored calculation fields take this cost global and substract sum(Payment Schedule::PaymentAmount). A data entry script ensures that the user cannot enter a value greater than the result of the calculation. When the unstored calculation field reports 0, that means the payment schedule is set up completely. So far everything is working fine.