Insert text in (calculated) field that is empty

[db_tragic]

OK, I should be able to figure this out, but I can't.

 

Have a field "AmntDue" which is defined by the calculation '= subtotal - paid'.

 

This is for an invoice, related to a visit consult.

 

I want to rework the definition of the "AmntDue" field so that if no invoice has yet been created, i.e. is empty, is show the text "No Invoice"

 

Not sure if I should be using the IsEmpty function, Case function, or both (or neither!), and how to structure the calculation.

 

I figure the calculation needs to perform the 'subtotal - paid' calculation first, and then run the IsEmpty function on the result of that calculation.

 

Thanks in advance

 

 

[doughemi]

On the assumption that subtotal is only populated when an invoice is created:

 

Case(

IsEmpty(subtotal); "No Invoice";

subtotal - paid

)

 

Otherwise, substitute another field that becomes non-empty when an invoice is created in the IsEmpty function.

[eos]

Otherwise, substitute another field that becomes non-empty when an invoice is created in the IsEmpty function.

 

Safest bet would be

 

IsEmpty ( Invoices::pk_InvoiceID ) … 

[db_tragic]

Thankyou folks,

 

The Case function, combined with using the InvoiceID as the test worked spot-on.

 

One glitch though.

 

There is a last line in the portal window, which has no corresponding data it refers to, however, I still get the "No Invoice" text displayed on this line.

 

I gather portals have a blank last line in them. Unfortunately this causes the calculation to still place the "No Invoice" text in the 'Balance' Field (The only other field appearing in this portal is the VisitNotes::Date field - which is blank on this last line).

 

Suggestions for removing the calculation result from the last line? 

 

Perhaps add another 'test' in the calculation that looks for a date value??

On the assumption that subtotal is only populated when an invoice is created:

 

Case(

IsEmpty(subtotal); "No Invoice";

subtotal - paid

)

 

Otherwise, substitute another field that becomes non-empty when an invoice is created in the IsEmpty function.

 

 

Safest bet would be

 

IsEmpty ( Invoices::pk_InvoiceID ) … 

[doughemi]

The blank last line in the portal comes from the fact that you have "allow creation of records in this table via this relationship" checked in the Edit Relationship dialog.  If you do not need to create child records by typing in the blank line, you can uncheck the checkbox and the blank line is not shown.

[db_tragic]

Worked a treat….thankyou!

 

So to clarify - does the option of "allow creation of records….." apply to record creation via portals, as a rule?

 

Haven't come across the need to address this concept before, so just after an understanding of how/why it is commonly used.

 

Thanks

Chris

The blank last line in the portal comes from the fact that you have "allow creation of records in this table via this relationship" checked in the Edit Relationship dialog.  If you do not need to create child records by typing in the blank line, you can uncheck the checkbox and the blank line is not shown.

[doughemi]

So to clarify - does the option of "allow creation of records….." apply to record creation via portals, as a rule?

 

It's just an option that is available to you as a designer.  For example, if you wanted to record a payment for a customer without having to jump to another screen, you could allow creation of records and set up a portal on a customers layout that displayed payment date and payment amount.  Then you can simply call up the customer and enter the payment on the same screen.

 

Other times, you just want to display related information, without adding more, in the portal. In that case, you would disallow creation of records in the relationship.

 

In your example, if you wanted to enter information into a new record in the portal, you could change the relationship.  Add a calculated field in customers PortalKey = 1.  Add a field PortalFlag in the child table, with an auto entered calculation =1.  Add parentTable::PortalKey =  childTable::PortalFlag to the relationship that the portal belongs to.  Then, you can use the blank line to add information, but the AmntDue calculation won't kick in until you type something in a field in that blank line in the portal (no record actually exists for that line until something is entered.)