Simple relationship not working

[North2Alaska]

I have two TOs, Home and PO. Home is a set of global fields, one is a calculation labeled gZero and has a calculated result of zero. In PO, I have a calculated field of zc_InvoiceUninvoiced, which is the amount used on the PO minus the amount already invoiced to give a value of what has not been invoiced. This was a quick and dirty way of tracking invoices without a full blown invoicing system. Anyway, the number that is displayed is like $450.00. I then have a relationship between Home and PO like gZero < zc_InvoiceUninvoiced. The problem is no records are every displayed in the portal. Are there any caveats when relating numbers or calculations or some such. I just can quite figure it out.

[QuinTech]

Hi, Jesse. Make sure they are both number fields (whether calculated or not) to start with.

Jerry

[LiveOak]

In order for a relationship to work the field on the right side of the relationship (at least) must be indexed. What this means in your case is the calculated field must be stored (not just calculated as needed) and not based upon any related fields.

-bd

[North2Alaska]

Yes, I have checked that one. They are both numbers.

[North2Alaska]

This may be the answer as the PO field is based on a sum of the invoiced amounts. How can I circumvent this requirement? Could I create another field with a auto entry calculation of the real value?

[LiveOak]

This will work.

-bd

[North2Alaska]

I gave it a try and it doesn't work. It doesn't seem like the calculation is firing because the value of the field never changes. Is there a trick to get it to do the calculation? I have the set it up as Number field with an auto-enter value and have checked off "Prohibit modification".

[North2Alaska]

I just can't seem to get it to work. The Auto-Entry does not fill in the value not matter what I try. There seems to be nothing I can do to force the relationship. Any others ideas?

[-Queue-]

Did you deselect 'Do not replace existing value for field'? This will force it to update whenever any field on the record is changed.

[North2Alaska]

Yes. I did that. Still does not calculate.

[-Queue-]

Did you modify any of the fields in the record afterward? I might not have been explicit enough about that part.

[North2Alaska]

Yes. I modified several of the fields being summarized. I also tried a custom function in hopes I could force the field to be indexed and still reference the related summaries.

[-Queue-]

Could you stuff and attach a small sample of your file?

[North2Alaska]

OK. Here is the whole thing. Please be gentle. You can see the results by going to Purchase Order and selecting the Invoice tab. Add or remove an entry to cause the invoiced amount to alternate between 0 and some positive amount. Go back to Home and you will see no change. If you click the refresh button it runs a script to store the calculation and thus changes the rows in the portal. Reverse the process. No matter what I do to the invoice, nothing changes.

CA_Tracking5.fp7.zip

[-Queue-]

I need a login.

[North2Alaska]

Yes. Sorry! Admin/admin.

[-Queue-]

I didn't have much time to look at it yet, but it seems there may be too many related calculations that are occurring after the auto-enter calculation. You would have to change something in the PO table for it to update anyway, since what you're changing is in the related POInvoice table and that doesn't affect the PO table. I would suggest having a script set the UninvoicedRef field with the InvoicedUninvoiced amount after modifications are made.