Invoices::Customer_ID = Companies::Customer_ID
If ( Not IsEmpty ( Invoices::Customer_ID ) ; "Client" ; "" )
(And you can leave out the ; "" at the end and get the same results.)
Invoices::Customer_ID = Customer_IDIf ( not IsEmpty ( Invoices::Customer_ID ) ; "Client") It tells me that function "If" cannot be found
when it's only this: If ( not IsEmpty ( Invoices::Customer_ID ) ; "Client"), Companies::Category does not change when I create a new record with the ID in the Invoices table.
The second is the correct syntax. The first line was just my re-iteration of the relationship I think you have defined.
This should be used in a field of type calculation, not in a text field with an auto-entered calculation. Auto-entered calcualtions do not automatically update when a referenced value from another record or table changes.
If I make the category a calculation field though, I can't enter my own text into it, correct? I have a drop down menu that lists a bunch of other types of businesses that wouldn't be clients, but rather utilities or stores, etc, so I still need to be able to enter other things into the field as well.
Use a conditional perhaps Phil? Just chiming in to see how this one is solved. =)
In which case you can't use either an auto-entered calculation or a calculation field.
I'd use a script to update this field with this text. You should be able to use a script trigger on an invoices layout that updates this field value the way you want either each time a company is selected on the invoice and/or each time the invoice is comitted.
would I use this same formula for the script triggor?
No you'd have to write a script that accesses the correct company record via find or relationship and then uses set Field to update the field.
I deliberately haven't specified the details of this script because the design requirements for this problem keep changing. The details of the script will depend on how you need to use that drop down formatted field.
You can also avoid the need for a script if you use a calculation field to selectively combine data from your drop down field.
IF ( IsEmpty ( DropDownField ) and Not IsEmpty ( Invoices::Customer_ID ) ; "Client" ; DropDownField )
This turns "client" into a kind of default value to be displayed in the calculation field if there is an invoice record and no other value has been selected in the drop down.
ok. I'll try this and the script idea and see if that works.