Question about merging multiple fields...

[FreedomFighter]

OK, thanks to everyone here and another go at the manual, I figured out how to set up my Delivery Memo with a drop-down list of vendors from all of my contacts in a 2nd "Vendors" database. Now I can click on the field and my list of contacts comes up automatically for "Ship To:" and "Received From:" fields on the delivery memo.

The next problem:

In the referenced "Vendors" database (which is years old and has 600+ records) the name and address information for each vendor is split into 4 different fields: Name, Address, City/State/Zip, and Contact name.

Like this:

Field 1: IBM Corporation (Name)

Field 2: 4334 Jimmy Joe Joe Way (Address)

Field 3: Altanta, GA, 96840 (City/State/Zip)

Field 4: John McGavery (Conact)

Of course, I need the entire contents of those 4 fields to appear in the "Ship To:" and "Received From:" fields on the Delivery Memo, so I can click on the field, go to "I" for IBM, and call up *all* the information in one contact field such as this:

Ship to:

IBM Corporation

4334 Jimmy Joe Joe Way

Altanta, GA, 96840

John McGavery

Is there a way, using the drop-down menu as I am, that I can either reference all of those fields ****or**** easily merge those 4 fields into one in the Vendors database, so when select one from the drop-down menu, all the info pops up into the "Ship To" field?

Talk to me like a newbie, cause I am!!!

MANY THANKS!!

[ December 26, 2001: Message edited by: FreedomFighter ]

[neile]

hi,

you could try:

in the vendors db, make a calculation field that joins the name and address:

calcfield=field1&"P"&field2&"P"&field3&"P"&field4

(where the "P" is the paragraph mark from the text operators group in the 'Specify Calculation' window.)

In the delivery db, define a relationship to the vendors db, based on the selection from your drop down.

a) In the delivery db, define a text field that looks up the calcfield in the vendors db when you add a record and select a vendor from the drop down.

OR

: Display the calcfield on the layout in the delivery db.

I would use option 'a': your record would display the address info at the time it was created. With option 'b', the address info in your delivery db would change if you update the address in the vendors db.

Hope that helps,

neil

[FreedomFighter]

Address Summary =

Vendor & "

[Rigsby]

No!

Sorry for the blunt answer, but a drop down uses the index to build itself, which means that each line in a field after a return, has its own index number and will thus be treated as a separate line item (drop down entry). But why do you need to see the whole address in your drop down. You should not be using your address field as a drop down.

[FreedomFighter]

Ahhh...well, I don't need to see the whole address on the drop down...just the vendor name. But when it prints, I need to see all the vendor info, including the address.

If you think about how a delivery memo is set up, it would make sense. Each delivery memo has a "Ship To:" field, which uses the same 20-200 addresses over and over again. Since that info comes from a database, it's kind of stupid to have to type it new each time, particularly when logging-out lots of inventory to clients or logging-it-in from vendors.

Being able to call that info up automatically would save tons of time and would reduce human error entering the info as well....a particular concern because a wrong zip code could end up shipping things to the wrong place.

It seems like a basic function of a common database need, forms that have repetitive information in multiple lines on a field, such as an address, yet I can't seem to figure out how to do it....

Also, the ability to print that out on standard labels...

Take a messenger center, for example, always sending things to the same clients and receiving them from the same vendors, or any warehouse...all of whom include name and address info on every form.

[ December 28, 2001: Message edited by: FreedomFighter ]

[Rigsby]

I

[FreedomFighter]

Yes, I think that does. What I'm missing is the CID. As I said, I've never created value lists in Filemaker...this is my first time...so understanding how to do this from the manual/help files is a bit of a small hurdle.

I put this fancy example together earlier:

The CLIENT database, compiled over years, currently looks like A.

I want to be able to select a CLIENT from the dropdown in the DELIVERY MEMO database, as shown in B.

All the CLIENT info should then pop into the "Ship To" area, as seen in C.

I'll try to create a CID for the Clients, then follow your other instructions, and see if that works.

THANKS as always! Help is appreciated for my newbieness.

[ December 28, 2001: Message edited by: FreedomFighter ]

[Rigsby]

Ok! I sent you an Email with 2 files. Just start the MAILINGS file, and check it out. It is pretty much what I described above. Hope it helps.

[FreedomFighter]

Ahhhh...you're great! Thanks.

It works!

The only things I had to do differently were:

1) I had to make it a pop-up list, rather than menu, since I have 650 records and needed to use the type-ahead-style feature, rather than just scroll down through all those entries.

2) Since I couldn't retroactively assign CIDs (to my knowledge) to all the records that had already been created, I imported them into a new "Clients" database.

After a little bit of jerking things around, I got it to work as intended. THANKS!

Thanks!

[ December 29, 2001: Message edited by: FreedomFighter ]