First & Second Field Combined

[FriarJnr]

I am really confused over a certain aspect of Value Lists!! I have a field that contains a value list of customer company names and a field that contains a value list of supplier company names. I want to be able to perform a search for all companies whether supplier or customer so I wanted to combine both lists so when a user did a search a drop down list would appear showing all companies. This looks simple enough as in the 'Specify Fields for Value List' window you can 'Use values from first field' and 'Also display values from second field', yet when I select the second field option only the values from the first field are shown! Even the description at the top of the window contradicts itself by saying you can 'Display values from the first field, second field, or both fields' but then says 'Only the values from the first field will be stored or applied.'. Makes no sense to me. Please help....

[David Jondreau]

It's not contradictory. The real purpose of that feature is to enable a user to choose or view a text value, like a company name, but really enter or store the numeric key for that value. They might choose ACME from the list, but the number 22 is entered in the field. It's very useful.

I think only one value is being shown because you're concatenating two names and the result is too long to fit in your field or because records don't have a value for both supplier and vendor.

Also, fields don't contain value lists. They use value lists for data entry and display.

It's unclear to me what you're trying to do. Do you have two fields in one record, one for Supplier and one for Vendor? What field are you searching on? How are the Supplier and Vendor value lists generated?

[Raybaudi]

... as in the 'Specify Fields for Value List' window you can 'Use values from first field' and 'Also display values from second field'...

... but fields of the SAME record !

I don't know if you have two tables:

customers

suppliers

OR

one table with a description field.

[comment]

... but fields of the SAME record !

I don't want to confuse the issue even more, but you CAN display values from a related field.

[Raybaudi]

yes, values from a field of the same record or from a field of the related record...

but I think that FriarJnr is asking for a completely different thing: some way to obtain a value list from 2 value lists.

[LaRetta]

I want to be able to perform a search for all companies whether supplier or customer

And of course the real answer is that both should be RECORDS within the same table with a TYPE field whether they are customer or supplier and not in different files or tables. What if a supplier decides to buy something from you? As you have it, they would also need to be set up as a Customer, duplicating all their supplier information (such as address, phone). And as you are finding out, if Samuel Jones calles in and the receptionist wants to quickly find out whether they are a customer or supplier, they must search two fields.

Any time you have like entities they should be in same table. Both customer and supplier have street address, city, state, zip, phone etc.

LaRetta

[FriarJnr]

Hi guys, thanks for replying. Sorry for delay, been on other projects for a while!!! Seems like I totally misunderstood what that feature did!!! I think LaRetta is right. I currently have Suppliers and Customers as two separate tables and I was trying to merge the Supplier and Customer values list which I guess can't be done. I think LaRetta is suggesting that all Suppliers and Customers should be in one table and differentiated by a radio button to distinguish the two and just have one value list for them all. Is this correct?

[LaRetta]

Yes, that is my suggestion. You may even want to use a checkbox because a company COULD be both. Then when you search, use your checkbox. If a supplier is also a customer, they will still appear in your customer lists.

Use caution when combining the tables if your Suppliers have child records, such as using the SupplierID in Product records or another table for multiple addresses or numbers. You won't want to break those relationships when you reassign or combine them.

If you need assistance working through the transition, let us know.

LaRetta

[FriarJnr]

That's great thanks. Will tackle that now and will take care as I do have relationships to a products table!! A do have another related question though, I'll try to explain it clearly. Are values list editable via calculations? For example, I will now have a value list of all company names, could this list be altered to show only customers or suppliers? Cheers.

[LaRetta]

I will now have a value list of all company names, could this list be altered to show only customers or suppliers?

Absolutely. It is called a conditional value list. There are many examples here on Forums but here's a quick example (attached). Instead of the condition being static (as in this case), it can be based upon different fields if you wish. It depends upon where (and how) you wish to use it.

ConditionalVL.zip

[FriarJnr]

Absolutely. It is called a conditional value list. There are many examples here on Forums but here's a quick example (attached). Instead of the condition being static (as in this case), it can be based upon different fields if you wish. It depends upon where (and how) you wish to use it.

That great thanks, but I'm afraid I have another question! I've changed the Name field to a drop down list and that list changes depending on whether it's a supplier or customer, but if I go into Find mode then the drop down list is empty and this is how I wanted my users to be able to search for companies. Any advice?

[LaRetta]

For the sake of demo, the popups were attached to a global field. Globals do not work in Find Mode. So attach the value list directly to the Name field instead of the global. Or if you wish, use the global as such:

Allow the User to insert the name into the global via the dropdown then click a find button which runs the following script:

Set Error Capture [ On ]

Enter Find Mode [ ] ... uncheck pause

Set Field [ realNameField ; globalField ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ "No records found." ]

Halt Script

End If

Set Field [ globalField ; "" ]

Why use a global? Well, if you are not careful, a user may think they are in find mode when not and CHANGE the name by accident. I always use dedicated find layouts and control finds very carefully. If nothing else, colorize your layout during find mode or use conditional formatting to produce the word FIND at the top so Users are never in doubt.

Notice that I let the User enter data into the global before Find began ... you then take the global value and set the real field once find has started.

Edited November 5, 2008 by Guest

[FriarJnr]

Thanks for the help. I've now got the value lists working for 'suppliers', 'customers' and 'all companies'. I've now attached the value list to a normal (indexed) text field and the drop down list works in Browse mode but I now have 2 more issues I'm afraid. When I select a company name the contents of the field is replaced with the company ID and not the company name that was selected. Also, the value lists still don't appear in Find mode. Any ideas of where I may be going wrong? Thanks.

[LaRetta]

Popups always insert the ID instead of the name - that's the way it should be, ie, you should always store the ID instead of using a name. As I said above, globals do not work in find mode. I suggest you attach your file if you are still getting stuck after re-reading the thread. :wink2:

[iwasnevy]

Hey - I ran across this while searching to solve a problem. I'm hoping you guys can elaborate on this feature for me.

Here's the scenario - it's pretty simple. I'm entering data into the field using a drop down list. The data being entered is the ID for that record, but the user selects the name from that record instead.

I've got the whole value list-enter values from first field but display values from second field thing worked out - it's properly displaying the values from the second field (names) in the drop down list.

The problem is that once I select the name in the drop down list and move to the next record, the field shows the ID number, not the name. I don't even want the user to see the ID number. This is driving me insane! help?

[EDIT] LaRetta, I just re-read you last post and realized that it directly answered my question, soI apologize that this is redundant. You're saying that there's no way to make the field display the NAME after it's chosen from the value list as opposed to displaying the ID number of that record? Seems like that's a significant flaw in the PRESENTATION of the data that's being entered.

[EDIT again...]

Never mind, I found my answer here:

http://www.fmforums.com/forum/showtopic.php?tid/199475/

Thanks! :

Edited November 26, 2008 by Guest