Relationship with foreign key on calculated field

[obiobson]

Hey everybody,

I did my best to find a solution to my problem in the existing threads, and spent hours while searching. So I hope I was not just too blind to find anything ...

I am in the process of building an FM database which is basically dealing with all relevant tasks of a small business. Like this, you can put together invoices and you can register payments and link them to the respective invoices. The invoices and payments tables are linked by a join table, which not only contains invoice-ID and payment-ID but also a specified amount, so that one payment can be linked to several invoices (i. e. split up), each link being attributed a certain amount of money.

In the invoices table there are two calculated fields, one adding up all payments being linked to the invoice, and a second comparing the total amount due of the invoice with the actual payments and then generating a status like "unpaid", "paid" etc. So far so good.

What I want to do now is have a portal on the payments-layout display all UNPAID invoices, so that the user can choose the one he wants to link to the respective payment. Displaying ALL invoices could become very inconvenient once you have hundreds of them in the database (and hopefully only a few dozens are unpaid).

The problem is that in the relation the foreign key (i. e. the "status" field) is a calculated field. Like this, it is not possible to filter the display of the portal by means of the relationship so that only records with a status of "unpaid" display.

I hope my English is good enough to explain the problem. Your pro advice would be very much appretiated.

Thanks.

Obiobson

[iMarcW]

There might be a better way to do this, but one way to do it is to create a "constant" field (like a global but easier to set/change when database is on a server). Call it gUnpaid, or whatever, and make it a calculation field with the value "Unpaid" (just like that, in quotes). Then, when you create a portal filter, the relationship can look for invoices where gUnpaid in the main table is equal to "Payment Status" (your calculated field) in the related table. (And there would be another key to match the invoice to the customer, which you probably already have in place.)

Hope that makes sense.

Edited May 6, 2008 by Guest

[Fenton]

It is not so much that the field is "calculated" that is the problem, it is that it is an "unstored calculation"; but it comes to the same thing. An unstored field can be the originator of a relationship, just not the target. So you can use that fact to "tunnel" thru Invoices, to only "unpaid" ones. This method is (fondly) called "Ugo's method."

The link from Payments is, I think, most intelligently done with the Client (or Customer) ID. A payment is always from a particular person. There is no real reason to try and access all unpaid invoices, when you're trying to apply payments from one person.

So, in Invoices, you can have an unstored calculation which, instead of producing "paid or "unpaid", produces the Invoice ID when conditions for paid are not met,* otherwise nothing.

Then use that unstored "client id if not paid" as the originating side of a relationship to ANOTHER table occurrence instance of the Invoice base table, targeting the Invoice ID. Now you have a filter to show "non paid invoices for this client" from Payments.

*It's best to recreate the calculation tests locally within the calculation, rather than referring to another field. Because it's a little bit faster to process. But either works.

[P.S. This post was post-edited, to fix the (gross) error comment pointed out in his post.]

Edited May 6, 2008 by Guest
InvoiceID, not ClientID. Duh

[Fenton]

It seems to me that you have "half" of a transactional system in place. You could run a script that would loop through the "unpaid" invoices for the client, create the link(s) to the payment, and mark the invoice "paid" if the total payments ≥ its balance due. Then the "paid" flag would be stored, and you wouldn't need the method in my earlier post. But the loop script would have to be very reliable. So your method is also good, though somewhat manual.

[comment]

So, in Invoices, you can have an unstored calculation which, instead of producing "paid or "unpaid", produces the Client ID when conditions for paid are not met,* otherwise nothing.

Then use that unstored "client id if not paid" as the originating side of a relationship to ANOTHER table occurrence instance of the Invoice base table, targeting the Client ID. Now you have a filter to show "non paid invoices for this client" from Payments.

It seems to me a ClientID, once produced, would then fetch ALL invoices of the client, paid or unpaid. I believe the unstored calculation needs to return the InvoiceID when unpaid, which then relates to itself in the second TO.

[Ugo DI LUCA]

Hi,

Relying only on unstored calculation may indeed work, but I'd rather follow Fenton on that one. An invoiced flagged as paid with a flag is surely a better choice, specially when later you will need reports, alerts, on these invoices.

A script triggered when the amount input is equal to what is due for that invoice could be one solution, but as said already, you need a robust control of scripts. That's the second half of transactions ;)

[obiobson]

Hey guys,

thanks already for your fast and valuable input. Perhaps I might add that the system is working in the context of a theatre agency. So there is a bit of a different logic behind it, not being so much an "online-shop" thing. Like this, it was my intention that the user just types in the amount of the transfer and then chooses an invoice which is related to that payment. Once this link is established, all relevant data is hooked up with this payment, i. e. who sent the money, what theatre play this relates to and which author gets the royalties.

What I do not so much like about the idea of a flag is that it is not so "dynamic". If you can manage to establish a neat system (working like pipes or something) that relates payments and amounts to invoices, it does not need a fixed point at which a script is to be run or something like that. So if f. ex. you made a mistake like attributing a payment to the wrong invoice or giving a wrong amount, you can always change this and everthing stays in place - I mean you don't have to "reverse" the action of a flag script. Like this, in the invoices table, I have a calculated field which calculates (max-Function) the latest date of a payment related to that invoice. If an invoice is paid in several "slices" of money, this field tells you when the last payment was made and, once all the payments add up to the total amount, when the invoice was actually completely paid. Maybe all this is quite a stupid idea, but so far the mechanic of it feels quite cool.

@iMarcW: This is basically how I tried to solve this. But given that a calculated field can obviously not be targeted like that in a relation, this left me with a blank portal.

@Fenton: I have read quite a few of your posts in this forum, and, wow, you have quite a bit of knowledge to share! Thank you.

I like "Ugo's method" (especially for its fancy name). As stated above, I am still trying to relate the payment to the "customer" not by choosing the customer directly but by choosing the invoice which the payement relates to. Again, I am not sure whether this makes sense at all. But supposed so, do you think one could modify the method so that the pipe produces not only unpaid invoices of one customer but ALL?

I have also wondered whether all this might be best solved by using a script. But as I said, I like the idea of something dynamic. If I used a script, I could also just flag the invoices in some way and then simply use a constant in my payments table that is calculated to "unpaid" or something similar and then use this for the relation, as proposed by iMarcW.

I hope this is not too confusing. Thanks!

[Ugo DI LUCA]

Hi,

Ok, the U-go key may be a way to go, it's fancy enough I may admit

However, whatever the field of business, the concept remains the same and yours is not odd indeed as we cross it in quite every business application.

Surely the script triggered when the amount is *edited* must handle both ways ( reversed too ), but it's surely what will render your application solid at the end.

We all hoped that a bunch of calculated values could do it for an inventory system, but at the end, as Fenton said, it's transactional and denormalization may be expected for such processes. For matter of performances in some cases too. For matter of business aspects in some others.

Won't it happen that a client would pay 49,99 instead of 50,00 and you'd consider the invoice paid anyway ?

Anyway, I like your logic, the way you read it, and I'm quite sure once the calcs are done and everything is automated, you'll find your way to some scripts ( that finally will use the same calc definitions )

Ciao

[comment]

An invoiced flagged as paid with a flag is surely a better choice

Just a note to say that I disagree with the above statement - and all that follows from it.

I don't think this is the correct place to debate the issue, but I would like to ask you to clarify what you mean by "business aspects" when you say:

We all hoped that a bunch of calculated values could do it for an inventory system, but at the end, as Fenton said, it's transactional and denormalization may be expected for such processes. For matter of performances in some cases too. For matter of business aspects in some others.

I believe I understand the "performance issues" part, but what "business aspects" would demand denormalization?

[Ugo DI LUCA]

Denormalization is not the correct word I'm afraid, and has nothing to do here, thanks for pointing it. The relations established here and well explained are all correct to me.

I was refering to a scripted approach vs an automated one, where finally the result passed to an indexed field would be exactly the same ( even evaluated in this same normalized structure ) that if it was a calculation.

It has everything to do with performance for first, as unstored calculations, moreover when used accross related set of data, would slow things drammatically.

It has to do with some processes that would need adjustments, where the it's better to not deal with a ALL CALCULATED solution ( i.e. Inventories )

As for the invoice flagged as 1/0 , Paid/Not Paid, indexed rather than unstored, I think it's rather obvious that it's easy to find or establish a relation to paid/unpaid invoices when the key is indexed.

There are occasions where I'd retain an unstored result as acceptable, but surely not in such cases, where, generally/often, a paid invoice becomes a locked document. The same as a shipped order is closed.

True that even a Privilege may be based on an unstored result, the related items could be locked according to the unstored state of the invoice.

But how many times does it happen that a client needs to *manually* unlock an order, a shipment, an invoice. If everything was linked to calculations, they should go and edit the related data. It's so easy to have it done with a flag, for sure considering the user that's doing this has the necessary privileges/knowledge.

Are you really thinking differently in such a case ?

Edited May 6, 2008 by Guest

[comment]

A semantic note: I think storing a result that CAN be calculated (and that would become incorrect if some data were modified) could be called denormalization.

Are you really thinking differently in such a case ?

Probably. I am not sure what you mean by "scripted approach vs an automated one". If you mean that "real" data should depend on scripts triggered by data modification, then yes. All the reasons you have stated are performance-related, AFAICT.

[Fenton]

Oops! comment is right, about the Invoice ID, not the Client ID (big duh for me). Yes, it should be Client ID to the Invoice TO, then the calculation field should produce the InvoiceID of those not paid. Then the next relationship should be from that calculation field to the Invoice ID (which is unique in the Invoice table).

[Ugo DI LUCA]

ok

"Wiki:

Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data. In some cases, denormalization helps cover up the inefficiencies inherent in relational database software as of 2008. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.

Then, yes, I go for de-normalization for sure. Not for grouping, nor adding redundancy, as this field ( flag ) stays the only necessary field. But its result is set by script rather than calculated. And I'm using the relational model as it is to set its result.

As I said, for performances issues, first. But also in some cases ( temporarily unlock/lock ) to offer a way to by-pass in some occasions some logic. With lot of control for sure.

[Ugo DI LUCA]

Me again,

I believe I understand the "performance issues" part, but what "business aspects" would demand denormalization?

I don't think this is the correct place to debate the issue neither, but...

One example

A client asked me to build a Business Application, where, as first, the rule was to respect a First In/First Out system in its shipments. That is that the client orders where shipped according to the order date.

So the theory was that if a client ordered 6 items and another a few hours later ordered 10, and there were 10 in stock, the second would wait until 6 new items would arrive from the plant. Everything could have relied on calculation based on the order date and the quantities on hold at this date/hour.

10 and 4 can be acceptable, but for sure, when you got an order of 900 and another of 6 and there are 900 items in stock, you'd favor the 900 instead of the 6 if you'd have to wait a few weeks to make the sell.

So giving a way to bypass the established rules by ticking the reserved box so that the other order gets the quantity when it is refreshed is an option the client finally asked for.

Edited May 6, 2008 by Guest

[comment]

nor adding redundancy, as this field ( flag ) stays the only necessary field.

The information that is stored within the flag field can be calculated at any time directly from the payments. Therefore, the flag IS redundant - unless you delete the payments.

I am not opposed to de-normalization per se - I have no illusions about Filemaker's ability to track thousands of cascading unstored calculations. It's the scripting part that I'm having a hard time to accept. The process is vulnerable, and it's very difficult to fool-proof against power outages, locked records and... that's the most difficult part: anticipating other possible disasters. It's a game against Murphy, and the odds favor Murphy to win.

[LaRetta]

a First In/First Out system in its shipments. That is that the client orders where shipped according to the order date.

Wow. Your understanding is different than mine. FIFO refers to inventory movement; meaning older stock always moves before newer. It is critical to good retail practice even for non-perishable AND it affects a business's tax state because Cost of Goods Sold will be less than Inventory at year end. When orders are shipped is a courtesy and simple good business practice but not FIFO, at least from my understanding of it.

[LaRetta]

The process is vulnerable, and it's very difficult to fool-proof against power outages, locked records and... that's the most difficult part: anticipating other possible disasters. It's a game against Murphy, and the odds favor Murphy to win.

I agree with Ugo on this one, if I understand the discussion properly, ie, not flagging with a 1 but marking in SOME way (actually using a DateInventoryPosted. Yes, it is more vulnerable but speed issues are too important.

I am talking about using an Inventory table. You count your total shipped by Product ID for the day and store the total in each product record. You run your Inventory which accumulates (via script) sold products for the day [color:green][color:green](writing to Inventory using Allow Creation on ProductID and Date). You then compare your opening count per product to the day's inventory for the product. If anything is off or goes wrong, you delete the Inventory lines for that day and can simply run it again.

Edited May 7, 2008 by Guest
Changed a few words for clarity (hopefully) & added green

[comment]

I agree with Ugo on this one

No, you agree with me (now isn't that a fine self-contradiction?).

I admit I didn't entirely understand the method you have outlined, but it seems like you are at least trying to steer your scripts to deal with metadata instead of real data.

[LaRetta]

Ha ha! Okay ... whatever you say. I thought it strange (although not impossible) that I would disagree with you.

Yes, it is metadata but it STILL is record marking with possibility of error. The marking would take place before write to inventory to freeze the individual lineitems (DatePostedToInventory) which would also be count/verified that they all wrote properly before writing the sum to Inventory. So there is a process verification of the process verification. And since Inventory generates a unique line per product per day, it is easy to audit track and rerun in case of disaster.

I must not have read the entire thread correctly then. My apology for stepping in. :wink2:

[comment]

it is metadata but it STILL is record marking with possibility of error.

That just means that the particular method is not perfect in achieving the goal of dealing entirely with metadata. But I believe the important point here is that you have set such goal.

My apology for stepping in.

It's not my thread... But I am afraid we may have hijacked it far away from obiobson's original question.

[LaRetta]

But I am afraid we may have hijacked it far away from obiobson's original question.

Ugo did it. It's good to have him back so I can blame him. :yep:

[Ugo DI LUCA]

Oh yeah, it's all my fault ?

Next time, I'd bother you with my accounting class and the LIFO method. You know, the Last In, Last Out, or better put when.... ah ah ah

You are fortunate, I won't explain how I'd use repeaters in this case. Not before Søren open his eyes :laugh2:

Edited May 7, 2008 by Guest

[fabriceN]

I had never realised USB was standing for Ugo Strikes Back ;)

[Søren Dyhr]

Oh yeah, it's all my fault ?

Carefull not to get stripped you moderator badge, you're supposed to be "primus inter pares"

--sd

[obiobson]

I was thrilled to follow your discussion and see where a simple relation related question can take you guys! No blame for hijacking, though!

So cutting a long story short, you strongly suggest to not rely on a calculated value in the relation, especially in regard of speed issues, but recommend to have a script set (and un-set) a flag and build the relation on that.

So, I think I will go for that and say goodbye to my "floating" calculated system. In the end, it is very true what Ugo said: In the real world, things might easily not add up as maths would have expect you, and you might be inclined to consider $49,99 a complete payment for a $50 invoice.

But to go on more general ground, there might be a situation where one would want to have a calculated field as the foreign key in a relation - which obviously is not possible. Is Ugos method some kind of general work-around there? Or is there something like a general solution to such problem? Or does it in the end come to the fact, that such wish always derives from a misconception in your database?

[LaRetta]

Oh, I understand LIFO and it is used quite a bit. But you'd better hope that the next chocolate bar you buy isn't based upon it. I'd rather pay a few more cents for it because FIFO is a bit more expensive on the manufacturer than to end up attempting to eat grey chocolate. Even with serialized electrical components; the one lonely item sitting on the back shelf which never moves but eventually gets sold to a poor consumer could, by serial, be three years old. LIFO saves manufacturers/retailers money - FIFO is good conscience.

UPDATE: Oh, Ugo, maybe you don't see/know what I do in the states; France may be quite different. Walk behind any larger grocer chain in the U.S. and watch what they put in the dumpsters nightly. They throw away a lot of good food because it hits 'sell by' date. But surely you've seen manufacturers with products with expiration dates MMYYYY stamped on them? Try selling that stock to a store when the exp date is 4 months in future. They won't buy it and again it gets trashed. We dump enough product in this country to feed the third world. Try telling a car dealership that LIFO is the way to go. //END RANT

I look forward to more of your repeater usage! :wink2:

Edited May 7, 2008 by Guest

[comment]

:backtotopic:

Is Ugos method some kind of general work-around there?

Yes, and it works quite well, provided the number of related records is relatively small. In your case, especially since you want to filter the unpaid out of ALL invoices, you can expect a gradual slowdown as the number of invoices increases.

Moving to a solution using a stored field has its problems too, since you need to make sure any modification to payments is followed by updating the stored field. This is difficult to implement in a reliable way.

The question whether $49.99 is a complete payment for a $50.00 is irrelevant here, because whatever answer you choose, the same logic can be applied to both methods.

Edited May 7, 2008 by Guest

[mharrsch]

I know this thread is a couple of years old but the problem I am experiencing is very similar and I tried using the Ugo method to solve it and I'm still having problems.

I have two related tables - Projects and Jobs. The client wants two views of the Jobs table available - one view for All Jobs and one view for Jobs in the current FY. An existing relationship, JobsProjectsAll, is based on Project ID and displays all related jobs.

Based on the information provided in this thread, I created four new fields in the Jobs table: CurrentFY which calculates the current FY based on Get(CurrentDate). The client has a FY that spans from July 1 to June 30 so the formula looks at the month and if less than 7 assigns Year(Get(CurrentDate)). If the month of Get(CurrentDate)is > 6 it assigns Year(Get(CurrentDate))+1. The JobFY field must evaluate the status field and the Delivery Date field. Jobs that are not cancelled and have a delivery date within the CurrentFY are to be visible in the CurrentFY record set. The client considers open jobs that float from year to year and have no status as CurrentFY. If Job status = "On Hold", the job is to be considered within the CurrentFY. If Job status = "canceled" the client no longer considers them in the CurrentFY. My Case formula evaluates the status field and the delivery date field and returns the appropriate FY value.

Then I have a field CurrentFYJob that looks at the JobFY and returns a Y if the JobFY & CurrentFY are equal. Lastly I have a field FYProjectID with calculation If(CurrentFYJob="Y";ProjectID;"")

I then created a new relationship called JobsProjectsFY linking the FYProjectID field in the Jobs table to the ProjectID in the Projects table. (Did I mixup my target and source for the unstored calculated field?)

When I created a test portal based on the new relationship it was blank. When I look at my calculated values on a layout based on Projects displaying related fields from JobsProjectsAll, the values are all correct. If I create a portal based on the relationship JobsProjectsFY, nothing shows up.

I reread the post explaining the Ugo method and noticed that it said to create another table instance of the parent table (if I understood it correctly) So I created a new table occurrence called ProjectsFY then related it to my Jobs table JobsProjectsFY using the projectID and FYProjectsID fields and changed the context of all of my formulas to be based on JobsProjectsFY instead of JobsProjectsAll. Instead of displaying data, the fields displayed

[comment]

I don't think you need the Ugo method for this because IIUC all the information required to determine whether to show a job in the portal or not is contained within the job record itself.

However, I got a little confused reading your description, so please clarify a few points:

1. The portal is on a layout of Projects, and it needs to show a subset of a project's jobs?

2. The jobs to be included in the portal are jobs where:

Delivery Date is in the current FY AND Status ≠ "Cancelled

OR

Status = "Hold"

Is this correct?

[mharrsch]

Yes, the client wants the portal to show only jobs in FY >= the Current FY that are not canceled and any jobs on hold. The portal resides on a layout based on the Projects table.

I changed the FYProjectID field to a text field with an autoenter by calculation of If(JobFY>=CurrentFY;ProjectID;"";) then wrote a script that can be executed at the beginning of each FY that resets the FYProjectID field with a Replace by calculation function and the portal shows properly. I just hoped there was a way to do it automatically so the client didn't need to remember to run the script at the beginning of each FY.

[comment]

Well, it was all quite simple until you changed the requirements to include future years...

Anyway, see if the attached works for you.

NotUgo.fp7.zip

[BruceJ]

Ok, as much as is pains me to say this, I have a quick and easy sure solution to exactly what you are trying to accomplish...

Two words...

[color:blue]QuickBooks

It will even link to your bank account, keep inventory and all that fancy stuff. I'm all for being creative and independent, but why re-invent the wheel.

It's like that old story about NASA spending thousands of dollars to develop a pen to write in zero gravity and varying air pressures.. very sophisticated. The Soviet Union faced the same problem, they used a pencil.

[comment]

:qwery:

[mharrsch]

Bruce, to keep my question straightforward, I did not include all of the information about the rest of the solution I am working with. This solution was designed by someone else some time ago and I have been asked to modify it to suit the client's current needs.

It is not a simple accounting solution that could be easily handled with Quickbooks but a media and design department's project management system that includes both cost and design and editing task management as well as an online component that enables the department's customers to request services that is integrated into the project and task management modules. Therefore, I am somewhat restricted in approach to working with a fairly complex database schema that I must be careful not to disrupt - especially since it is a heavily used live production database with thousands of existing records.

[mharrsch]

Thanks so much for your example. I'm still having a little problem, though, because the DeliveryDate field is a calculated field that draws its value from the related Tasks table field "Report Date". I changed the formula to refer directly to the related date field in the task table but the portal remained blank and I suspect it is the result of using a related field value.

I wanted to be sure I understood your use of the Month(DeliveryDate)>6. This increments the year if the Month of the Delivery Date is >6 because the logical evaluation Month(DeliveryDate)>6 is true (which equals 1 to the computer) if the Month of the DeliveryDate is 7 or higher. So you are combining a Date Function Year(DeliveryDate) with a Logical Function then a Text Function when you add the Paragraph Return with the Text "Hold" to include jobs with status "Hold". Correct? Very efficient.