Combining Two Value Lists

[iamthegrey]

Hi:

I have some legacy databases that I am working with and I am trying toa dd some functionality for my office staff. I have a Customer database with all contact info for my customers. I have a Vendors database with all my contact info for my vendors. I also have a Documents database that is related to both of the other databases. I frequently make documents to my customers, and I frequently make documents to Vendors. I use the Documents database to make documents to my customers and my vendors ( which sometimes refers to info from my customer file). However, I have just come upon a request for a new Document which could be directed to either customers or vendors -- a Thank you letter ( sometimes we want to thank our vendors as well as out customer ).

Now in all the other instances, I have a field called Vendor Name or a field called Customer Name, both are formatted as value lists, defined by the contents of those fields in the other files. What I need now is a value list that includes both of the other value lists and I am not sure how to go about this.

I have a feeling I need to use the ValueListItems function, but I havn't been able to figure out how to combine the two lists into one. Any help would be appreciated.

[QuinTech]

Hi, i think you're onto it with value list items. Make a calc field:

ValueListItems ( valueList1 ) & "" & ValueListItems ( valueList2 )

Now base your new value list of the contents of this field, and good luck finishing that jacket.

Jerry

[iamthegrey]

Jerry:

Thanks for your quick response, and I would love to tell you that it worked, but it didn't. My new value list displays the items from the first value list followed by a space, but no items from my second value lsit are present. Any thoughts?

By the way, twenty points if you can tell me where the jacket reference comes from...?

[QuinTech]

Take a look at the calc field. When you view that, does it contain all of the values you want, or are there some missing there? One good practice--i don't know if it has any bearing on this--is to use "Status ( CurrentFileName )" in any function that asks you for the name of a file, as does ValueListItems. I wonder if maybe there's something askew in the second half of your calc field.

By the way, twenty points if you can tell me where the jacket reference comes from...?

It's so hard to resist Google, but i'll take a stab. First, some questions: Is it a literary reference? If so, is it English (i.e., from the country of England)? I think i may have less success with this than i do with your value list question.

[iamthegrey]

I am not exactly sure how to implement the Status (CurrentFIleName) function as I don't see it in my list of available functions, but I did notice after going back and double checking as you suggested that, the file I was pulling the value list from, was actually pulling that value list from another file. So I changed the calculation to refer to the original file, and that seemed to fix it. Now all values are showing up. One final question -- I am assuming there is no way to intermix the two sets of values so that they all come out alphabetically? It seems crazy but I thought I would ask.

Actually, the reference comes from Hollywood, comedy film from the last 10 years -- think cowboys...

And thanks again for your help, Jerry!

[QuinTech]

Ah, version 7. I think the syntax you want is Get ( CurrentFileName ). The Status family from version 6 and prior has been replaced by the Get family. If that's not it, look for the most similarly-named thing.

The value list doesn't automatically come out alphabetically sorted? It does on mine. Possibly another v6-to-v7 issue, but i don't think so. How is the list sorted? Is it one list followed by the next, each sorted within itself, or what?

----------------------------

Well, Blazing Saddles is more than ten years old and i don't remember it too well anyhow... umm...

this is tough...

umm... Can you give me one more hint?

[iamthegrey]

You know what - I'm a schmo. The value list comes out sorted alpahbetically but the calculation field contents obviously do not. I was overlaying one field over the other and that is what was screwing me up. Everything is cool. Now one more question ( i just keep adding them on ) - In the other cases I have the file do a lookup for the customer address, phone number, etc. How do I now make the address field, etc know whether to look to the vendor database file or the customer database file for this lookup information? any thoughts?

Jacket Hint - Billy Crystal

[Lee Smith]

C I T Y S L I C K E R

[iamthegrey]

C I T Y S L I C K E R

!!!BINGO!!! We have a winner!

[QuinTech]

S**T! Lee beat me again!

As for your question, let me wildly surmise. I am assuming you want to enter a customer ID or vendor ID in one field, and have FM fill in whatever address is appropriate so that you can send your invoice. Set me straight if that's not what you mean.

I think what might work is to create a calc field that evaluates to

Vendor::Address & Customer::Address

Now this calc field will, of course, only display information from valid relationships, and thus will only ever display one address. If you set your editable address field to automatically enter that, i _believe_ that will do the trick, but i'm not sure of that because it hinges on a relationship ubt only indirectly.

Lee, any ideas, Mr. Smarty-Movie-Pants?

J

[Lee Smith]

I was just playing the jacket game, was there a FileMaker question in there somewhere?

My question is. Where is the clue of "Jacket" at?

Lee

[QuinTech]

ROTFLMAO (now THERE'S a graemlin i'd like to see)

It all started from iamthegrey's signature.