Display text in a number field or a number in a text field or...

[mluka]

Hi, all.

I have this nagging problem with displaying information (text AND numbers) in a field.

There's a "Product" table which contains a price field, defined as text, as per FM Help. It can contain an amount (2 decimal places) OR text (such as "N/C" or "Included", etc.).

Then, there's an "Order Detail" table which contains a quantity field, defined as numeric, a unit price (defined as text) which is looked up from the price field in the "Product" table.

It also contains an extension field which is the result of this calculation:

Case ( Extension = "N/C" ; "N/C" ; Extension = "Incl." ; "Incl." ; Round ( Quantity * UnitPrice ; 2 ) )

I've tried defining this field as a number and also as text.

Here's the problem:

When I'm able to get the field to display the text values ("N/C", etc.), then it also displays the numeric values (in the other records), EXCEPT that it truncates those values that end in a zero!!! Thus, an extension of 5.20 displays as 5.2!!! An extension that contains 4.95 displays as 4.95.

When I'm able to get the numeric fields that end in zeroes to display properly, then the ones that contain text don't display anything!!!

I'm totally frustated by this simple problem!

Thanks for any assistance you can provide!

[comment]

See if the atached helps.

Untitled.fp7.zip

[mluka]

Hi, Comment.

VERY clever! VERY clever!

It's certainly a very good step in the right direction.

I would have loved to be able to do everything in the same field but I should be able to figure out what to do to edit the data.

(Sometimes, the products have text that doesn't change: "Included" or "No charge" for instance.

But in other cases, the text is just a "place holder" to let the customer know that the item will be priced later; I don't want to display "0.00" or just blanks because that can lead to confusion as to whether the product is free or what...

Typically, what we do right now is to add a comment on the initial invoice, then modify the invoice later, when the price is finally known.)

I'll have to figure out a way how to handle the editing of the two fields when the time comes to put in a price... Hmm... Maybe I could just present the "Price" field and have some logic that says "if Price is not empty, erase the contents of PriceNote"

Anyway, this is a very good start and I thank you much!

Merci beaucoup, as we say here!

[comment]

Maybe I could just present the "Price" field and have some logic that says "if Price is not empty, erase the contents of PriceNote"

That should be quite easy to do, by making PriceNote auto-enter such calculation, with replacing turned on.

[mluka]

Yes, yes.

And I'll probably put "Price" over top of "PriceNote" (set so you can't enter it in Browse mode). That way, when "Price" is empty, the user will see the text of "PriceNote". When they need to enter the price, they'll be entering it in "Price"; this action will blank out the contents of "PriceNote".

Very nice.

(Tricky for someone who would look at this layout a few years down the road if there's no documentation. We'll document this for sure!)

Thanks again for your excellent suggestions!