Value List

[diggnified]

I was hoping for some help on a quoting solution I am working on. I am using Filemaker 8.0v3. In my database I have 2 tables I have linked which are Dealers and Quotes. In the Dealers table I have a number field called DealerNumber, its a number I assign to a dealer and then I have a field called DealerName. On the Quotes layout I want to either 1. have a drop down list where I can select the dealer number and have it fill the DealerName dropdown which is next to it or 2. have a edit box for the dealer number and when I type in that dealers number It fills the dealer name edit box next to it.

Again I have the tables linked and now when I go to the Quotes layout and create the 2 dropdown fileds DealerNumber and DealerName and go to field setup and create value lists based on another table field it will not display the data until I go to the relationship graph and double click on the relationship and check the add records using this relationship box. I do not want to do that because I already have the dealers all defined in the dealers layout.

Here is a screenshot of my relationship graph if this helps and I have attached a copy of my filemaker file. Thank you for any help you can give me.

Screenshot

Attachment

[Søren Dyhr]

You need to grap the related autoID for the link, in order to show a related value!

Take a look at the attachment!

--sd

BTW why isn't the lines in the quotes layout normalized?

QMmod.zip

[diggnified]

Søren Dyhr thank you for taking the time to look at my solution that is a great help. Now I can go through there and see what I am doing wrong.

"BTW why isn't the lines in the quotes layout normalized?"

I belive you are talking about the all the size1length and etc fields that I have in the table. I was not sure how to do this differently because in the granite business many calculations which I do have on the layout yet need to have the length width and height fields broken down so it can calculate weight, delivery and install charges which are based off mostly the length and height of a monument. I am sure there is another way to handle this but I could not wrap my head around it.

Thank you again for your help and I will go through the modifications you dids and learn them. Take care.

Best Regards,

Dave

[Søren Dyhr]

I was not sure how to do this differently because in the granite business many calculations which I do have on the layout yet need to have the length width and height fields broken down so it can calculate weight, delivery and install charges which are based off mostly the length and height of a monument. I am sure there is another way to handle this but I could not wrap my head around it.

Alright here's some wrapping!

--sd

QMmod2.zip

[Ender]

Maybe I missed something, but why not use a portal for those Quote Lines??

[Søren Dyhr]

You do, as a fact hardly miss anything except, that a portal here would ruin a tasty layout - that's all. Another solution would be inserting a placeholder table like the one in:

http://www.fmforums.com/forum/showtopic.php?tid/176396/post/204618/hl/calendar/

But a genuine portal with "...allow creation" would have this dangling empty record syndrome, that you perhaps can drill your users to love ...but this made a perfect opertunity to issue some socratic noise.

It's similar to only associate vivid image of a painter with a ladder, each time Pythagoras is mentioned - I would here stress that relations was much more than showing something in portals.

--sd

[diggnified]

sd thanks again. I got some time after work to look at your mods. Starting with the first mod you did which was create the drop down for the vendor name and ID. I do not understand what is different than what I was trying. Was this a relationship problem that I had?

For a second let us forget about the Dealer ID. What If I simply want to put a drop down menu in my quotes layout to display for example just the Dealers Name. I do not understand why I cannot do this. Any value list I try to create from a related table field does nothing. When I create the value list and go to browse mode it does nothing, I cannot even click in the field until I go to the relationship graph and double click on the relationship and tell it I want to be able to create records. I do not want to create new records because I will already have them entered in the dealers layout. I just want to display the information not create new information from a vlaue list. Can this be done? I do not understand why in Filemaker it allows you to create value lists from other fields but you cannot view the data until you instruct it you want to use it for adding records. Thanks again sd. Regards

[Søren Dyhr]

Why do we bother relating tables in the first place anyway?? You had in your original file pulled in a field belonging to a different table, this indicates that you apparently wish some kind of structural relationship between the tables, if not then ditch the relationships and make a redundant field in the quote table with a value list based on info from another table.

This is not the helatiest way of doing things seen from a normalized and relational point of view, but if you can't see the benefits and merely in vanity throws in relations because everybody else does it, then ditch them.

What then can be achieved by relating the tables? You can from each of the clients in the client table make a portal with a list of quotes given to that person.

Next issue relations solves is to store data in just one place so you avoid any syncronizations issues, this means that the field in the quotes doesn't have to usher in a copy of the username for each quote, but instead reference an entire set of fields (a record) by linking on the ID... This was earlier on an important objective when storage was expensive, but both then and today is the measures put into syncronizing an objective.

I do not understand why in Filemaker it allows you to create value lists from other fields but you cannot view the data until you instruct it you want to use it for adding records.

If you put a dropdown list in the related field on you original approach is it obvious to me why you need to turn "...allow creation" on the field, because it behaves like the first row in a portal.

A portal is a presentation of the related data not the data in itself. When you wish to put data in a portal will the writing be tunneled to the related tables data in a NEW record, since every thing in a layout carrying : in front of the field name is referenced, not actually stored in you present table.

If the relation doesn't allow you to write something in the related table aren't input posible in the field unless a valid keyvalue have established the relationship, and you have to fiddle with the key field instead to make it point at the correct related record's data! If you then persist on modifying the refrenced field based on the table's content will you actually over time strain your value list, since you might overwrite some data with other records values.

What goes on is if you writes something in a related table, from another is the application trying to store the recordID from the writing table into a field in the related table, to tell where it belongs to, and if it doesn't work will the application then try to get the related records ID over to the main table as a reference.

The first is the most common way, and if none of writings are posible will the error occure. This is why we need to deal with the ID's instead, to change the linking not real data.

--sd