If Statement to auto-populate field

[three11jeff]

Hi,

I have two DataBases one Contacts and the other Invoices.

I'm going to use the Invoice DB to print Invoices I have with customers. On the Invoices I need to include different types of contacts (IE: Primary, Billing, Support, etc). In the Contacts DB I have a feild for each contact type (IE: Primary, Billing, Support, etc.) which equal either "Yes" or "No".

On my Invoices DB I created a "Primary_Name" field. What if statement do I need to use if Contact::Primary equals YES then populate with Contact::Name?

Thanks!

[Razumovsky]

You have pretty much got it.

if(Contact::Primary = "YES" ;Contact::Name, "")

Look at the help file under "logical functions", there is a lot of good info there - many people usually go with the Case function, as you can specifiy many tests.

Also, you will want to add another table "Items" with an InvoiceID field, and relate it to Invoice.

InvoiceID=InvoiceID

Track each purchased product as an Item that is related to the parent Invoice

It will save you problems later on.

-Raz

[three11jeff]

Thanks for the reply and I have tried that same calculation but I get this error...

This field cannot be found. And it highlights ;Contact::Name

I know the field is there! and I've tried it with a few fields to verify and get the same message.

[Razumovsky]

Do you have a field named Contact::Name, or just Name?

If just Name, do you have a Table occurence named Contact?

Try manually selecting the fieldname from the list on the top left and see if that works.

It works fine for me.

[three11jeff]

Yes I do. And I have manually clicked on the field names in the list and I still get the same error

[Razumovsky]

Copy your full calculation and paste it in your reply. You probably have mismatched quotes.

[three11jeff]

Ok so I lied about the DB name and Field names so I was less confusing so here is what they really are...

DBs

Contacts = MEL_Contacts

Invoices = MEL_ANGEL

Fields

Primary = ANG_SystemEditor

Calculation...

If(MEL_Contacts::ANG_SystemEditor = "Yes"; MEL_Contacts::Name, "")

[three11jeff]

Is it a different Calculation for Version 6?

[Razumovsky]

Try creating a test field "Test" and use:

case(Test = "yes"; "yes"; "no")

I believe the if function has been around for a long while, but I do not have 6 on this machine.

[three11jeff]

I get this message,

This field cannot be used here because it would cause a circular definition.

[Razumovsky]

Sorry! Test should be a text field. The calculation should be in a diferent field. At least you didn't get the name error.

[three11jeff]

nope.

Now it says This field cannot be found.

[three11jeff]

I started a bran new DB and get the same thing

[Razumovsky]

Take a break, go for a walk, reboot, and try again. It sounds like 1 of 2 things:

Your brain is preventing you from seeing a typo (happens to me all the time)

Your file is acting wierd (also happens to me, but almost always it ends up being the first one...)

You could also post a sample file and see if someone with FM6 will test it for you.

Best of luck.

[LaRetta]

You are using ; in your calculations. Prior to vs. 7, you must use ,

... sometimes it's the very little things that trip us ... been there, done that. :wink2:

[Razumovsky]

See, exactly what I was saying. but turns out the problem wasn't with your brain but mine.

time for me to take a break and go for a walk...

[Fitch]

FileMaker 6, try using a comma in that calc instead of a semicolon.

Edit: I was too slow... what LaRetta said.

[three11jeff]

Ok, This works in one DB but now when I try to pull the info from another DB nothing happens when I enter the record. I do not get any errors when adding the calculation.

[three11jeff]

Ok, I know what my problem is but I'm not sure how to get around it.

In the Contacts DB I have more than one record that is tied to the "CompanyID" which is the relationship field.

It only pulls back one of the records so it populates maybe the Primary contact but not the billing or technical contact.

Hmm... Help...

Edit: Can I use a loop script? or will it continually find the first record?

Thanks for all the help.

Edited June 29, 2006 by Guest

[three11jeff]

Anyone?

[Genx]

God, FMP6? Anyone can feel free to correct me here but you could try get value list items (even though the actual function probably doesn't exist in 6). Ummm, otherwise you'll be stuck with going to the related records, looping through them, collecting the results in a global field (again if it exists) and then going back to your original layout and doing what you want with it.

Hope this helped (i know it really didn't but i thought i'd try anyway)

~Genx

[three11jeff]

Would you be so kind to share the steps to set-up this procedure?

Thanks,

Jeff

[Genx]

Easier than this, you might consider using global fields or unstored calculations holding constants to filter your relationships down further so you basically have false one - one relationships.

I.e. For your billing contact details you have a dedicated relationship only for extracting your billing contact details from a one - many relationship.

On your parent side, specify an additional field called constBilling or something. Set it to be an unstored calculation, result "Billing". Relate this to your type of contact field (i'm guessing you have one) in your child table.

This will effectively filter your relationship down to one value - your billing contact.

If this wasn't exactly what you were after, re-post and i'll try what i was originally thinking. however at the moment it seems unecessary and/or irrelevant.

~Genx

[three11jeff]

Genx,

If Im understanding you that is exactly what I did. The problem was that it only found the first record in the contacts DB. So, If "John" was the first record from "Company#1" and set to Yes for Billing and Primary my contract will populate with that information, but "Bill" who is the Technology contact will not be populated.

[Genx]

Well no, your right he (Bill) won't get populated into your field hence why you need to add an additional relationship for your tech contact - this could all be done using recursive get nth record functions in FM 8, but still, your wanting to extract only particular entries if i'm correct, hence, for each entry you want to extract, you need an additonal relationship in order to identify the record you want.

E.g.

Contact Table:

PrimTechContact = "Yes"

Other Details = "Some Text"

Relates to your main table via an unstored calculation with the entry "Yes" --> this in turn allows you to isolate this particular record using only relationships.