Inventory/Payment System

[bleapy]

HI. I'm fairly new to Filemaker Pro, but have used it well enough to create a decent Relational DB. However, I still consider a newbie.

I'm using FM12 Adv. , I'm in the process of modifying the Invoices starter solution, by adding some calculation fields and adding Payments and inventory to it. The issue I'm having is that the INVOICES::Payment Status is a simple Text field, however, I want this to be an Calculated field (ie If(Payment::Total >= Invoices::Grand Total; "Paid"; "Unpaid"). This works, however, there are 2 TO of the Invoices Table (Unpaid Invoices and Paid Invoices) these 2 TO are connected to the Customers table, to query up any invoices that are unpaid and paid. When I change the Payment Status field to Calculated field from Text, this relationship is broken. How can I fix this? I've tried creating a Text Field, with Auto Calcuated to =Invocies::Payment Status, but that did not work. Suggestions?

Thanks.

[eos]

The relationship is broken because a calculation referring related fields can't be indexed, and therefore not be used on the “right” side of a relationship; using a text field with auto-entry doesn't work, because auto-entry does not “look” into other tables and therefore won't be triggered when you add a new payment.

I suggest using a script which after each Payment entry goes to the associated Invoice record and updates the Payment Status field according to your calculation; this way Payment Status can be indexed.

[bleapy]

EOS.. thanks for the clarification. Your suggestion worked like a charm. I created a simple IF statement to check the total payments and set the invoice status to Paid or Unpaid. And added it to the On Record Load script trigger. I should've asked for help sooner, I was racking my brains about this for a day! ..

Thanks.