7 Replies Latest reply on May 31, 2011 7:55 AM by JessOttman

    CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS

    JessOttman

      Title

      CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS

      Post

      Hi, Im creating a database that assembles hoses and parts using conditional value lists and graphics. I have a table called data entry this is where all my input gets added.

       So i have sizes 08,12,16,24,32. Lets say im doing 08 first,then i have the name of my hose which is in the same record as 08 next i have my fittings so lets say i have A B C D E all against 08.So i have five records altogether with (08,hose,A)(08,HOSE,B) and so on.

       Now i have around ten fields that eliminate data when the previous is selcted but i wont go through all since i only have a problemwith my  material field.In this i put certain material finishes against the complying fittings, ex.A=1,2,3 B=1,2,4 AND C=2,4,5. Each material finsh is added to data entry as a record. so 3 Records might look like:(08/hose/A/1),(08/HOSE/A/2),(8/HOSE/A/3)

      So if i select 08 i should see only hoses available in that size :Yes

      i select hose i see all compatible fittings to the hose in that size:YES

      If however i select material finish every finsh option comes up. So if i select A as my fitting i should see 1,2,3 instead i see 1,2,3,4   Ive rechecked my input and the relationships an they are fine

      the relationship is set up as DATA ENTRY = HOSE ORDER(LINKED BY SIZE)

                                               MATERIAL = HOSE ORDER (LINKED BY NAME TO MATERIAL)

      I have a different table for each field eg size,hose,fitting left,fitting right, material and so on.that is linked to a field in the table hose order.

      Apologies if my explaination isnt clear but any help greatly appreciated as there is alot of work gone into this and its only a small problem!!!

      many thanks

                                              

             

      RELATIONAL_DATABASE.JPG

        • 1. Re: CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS
          philmodjunk

          So you select a "Dash Size" in Data Entry and you should only see value from Coupling material that have a record in Graphic Build Order with that dash size?

          What have you selected as the "starting from" table occurrence in your value list setup? From what table occurrence have you selected the fields that supply the values?

          From what I see here, the values would come from Coupling Material and the Starting from Table occurrence would be data entry. Is that what you have?

          If so, you may need to check and see what records exist with which values in Graphic Build Order and Coupling Material to see if you have some that match when they shouldn't. Also make sure that both Coupling Material fields are of the same type. They look like they should both be text, though I would use ID serial numbers for this if I were to design this system.

          • 2. Re: CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS
            JessOttman

            Yes dash size is my main selection from then onwards the fields filter themselves, up until i get to coupling material. Graphic bulid order is where i have all the methods and fields working.

            Im not sure if ive done this right but data entry is my starting table occurance and my values r put into data entry. In my other tables i have put in records for instance like my hose name and graphic are linked in each record. So a plain hose graphic is linked to a name called SSWR, the next record might say anti static with a representing graphic. Also it is required that there is a code against each item eg SSWRP=321. So in DATA ENTRY i also have dash size,fitting style, hose accessories, coupling material and flange material.all plan text fields.

             Im not sure if im making sense in describing my tables.Il attach a list view for one type of fitting TL 106 and it shows my structure for the rest of my records. In my data entry table i used a auto complete feature so when i was entering the data i would make any typo's.

            In my coupling material table i have the coupling material descriptoions against codes.eg 316 SFF1= 7

            Both coupling material fields are the same type, I definately messed up as soon as i tried to put in the coupling material up till then it was perfect.

            Thanks for help,

            • 3. Re: CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS
              JessOttman

              I created another database with just  2 tables in DATA ENTRY and layout view I put test records fo dash size 08 against TL 106 AND TL 016 THE reason for these 2 is they have differint copupling materials.

              EG.

              08     SSWRP   FLOATING RING    TL 106    (316 SFF1)(316 SFF1 DP),(316 SFF4), (316 SFF6),(316 SFF4-1,) (316 SFF6-1),(HASTELLOY C22), (316 SFF4-CP)  (10 IN TOTAL)

              08     SSWRP            NONE           TL 106=(SAME COUPLING MATERIALS) ONLY DIFFERENCE IS THERES NO HOSE ACCESSORY)(NOW 20 RECORDS)

              THEN FOR TL 016:

              08 SSWRP       FLAOTING RING     TL 016 (316 STAINLESS STEEL)(316 SFF1)(316 SFF1 DP) (3 IN TOTAL)

              SAME FOR NEXT SET JUST NO ACCESSORIES(6 IN TOTAL)

              Now i have 26 records in total.

              My first field shows all values from dash from my data entry table = 08 then all the rest of the fields are shown by related values.

              It all works perfectly : 08   sswrp   none   TL 106/TL 016(I CHOOSE TL 016)Then i should only see from what i understand is the three coupling material options i gave to TL 016 but i see everything!! which is 13 options.
              I hope this explains my problem slightly better that the previous post. Im not entirely sure if wat im doing is correct,
              thanks

              • 4. Re: CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS
                LaRetta_1

                "I have a different table for each field eg size,hose,fitting left,fitting right, material and so on.that is linked to a field in the table hose order.

                Apologies if my explaination isnt clear but any help greatly appreciated as there is alot of work gone into this and its only a small problem!!!"

                Unfortunately, Jess, this is not a small problem at all.  You should have ONE table for your parts with a Type field.  You will continuously hit walls and eventually realize you must change the structure.  I realize you have put a lot of work into this but it is work that mostly wouldn't have been necessary if structured properly.  If you change it now, it will save you a lot of time and effort.

                Added ... you also have a hose order but no hose lineitems.  You have fields within the Build Order when those fields should be related records (one record per item). 

                "Now i have around ten fields that eliminate data when the previous is selcted but i wont go through all since i only have a problemwith my  material field."

                Oh but you SHOULD go through that.  You do not need a Data Entry table.  You need a standard Invoices/LineItems/Products structure.  In your case, the Invoice would be the order build and the LineItems would be each part added to that build.  Honestly, I do not like being the bearer of bad news but it is important to say it.  We can help you change it; we all have headed down those dead-end streets at times and I would freely offer my services to get your base structure set up properly. 

                • 5. Re: CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS
                  JessOttman

                  Hi La Retta thank you for your response,I realize that alot of things in my databse are probably not set up properly and i would 100% appreciate any help to point me in the right direction.There is alot of work gone into it and it was working well but there is no point of going on and putting more work into it,if as you put it i continue to hit walls!!!

                  I have only being using filemaker for about a year fully. I have done a few projects and have learned alot from asking questions.I did a portal a while ago using hoses, id size, and part numbers do i need to start by setting it up like that?

                   I think my biggest worry is that i need to select a name eg hose and that graphic needs to be pulled.As are the fittings the right descriptions need to be on the correct graphic this is why i thought i needed so may tables to pull all of the different types. The layout where these graphics are displayed will be a hose assembly so (FITTNG::HOSE::FITTNG)To put it simply.

                   

                  Thank you for your reply again and i would greatly appreciate any help i dont mind if i need to re do it at least i will know for next time and it will be done right

                  Jess

                  • 6. Re: CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS
                    LaRetta_1

                    Hi Jess,

                    I am pleased that you have an open mind to change.  Ideally, you would provide the details of your requirements and let someone with extensive relational theory simply set the foundation in place, explaining as they go so you grok why the structure is established in that way.  I do not (yet) understand enough of your requirements to provide the base.  I, or anyone, would need to see sample data and even ask questions before we would be sure it is correct.  A good structure can be easily morph'd as your business dictates; an incorrect structure will sit rigid.  So even if we don't nail it 100% at first, it can be easily extended as needed. 

                    What would work best is for you to provide what you have (with some sample data) and then you briefly describe the purpose of what you wish to accomplish.  It would be like explaining your business to someone who didn't understand what you do and what your program will provide (your vision of it, so to speak); speaking as if to a new employee.

                    " I think my biggest worry is that i need to select a name eg hose and that graphic needs to be pulled.As are the fittings the right descriptions need to be on the correct graphic this is why i thought i needed so may tables to pull all of the different types."

                    Well, a Parts table can self join to itself (which is the way conditional value lists normally work) and a self-join is just the same as multiple tables except it makes things easier.  But I won't yet say that it ALL should be in one table, it's just that most of it should be.

                    I will send you a private message with my email.  If you wish to provide me this information privately, I would be happy to review it and make recommendations or you can provide a link to your information here so many people can make suggestions.  We are only talking about a base of tables and how they connect (addressing the 1:n or n:n relationships required) which would provide the best structure.  Once a good structure is in place, you can move quite rapidly on fleshing out the details and moving forward with the UI.

                    I am fine with however you wish to proceed and will provide input and assistance as I can (either here or via email).  This is not a solicitation ... my plate is plenty full now.  I just know how critical a good base can be in how easy/difficult the development AND in how happy/frustrated you will be when working in it.

                    • 7. Re: CONDITIONAL VALUE LISTS STOPS WORKING AFTER 3 FIELDS
                      JessOttman

                      Hi LaRetta,

                      I understand the structure used with invoices/lines/and products and have tried to create a test file using just those tables however i have not had any luck setting it up or getting it to filter either.

                      In my database my graphic fields are working perfectly i need this many fields because the grpahics have different proportions and need seperate fields. This has not yet caused any problems and their descriptions can always be changed when the filtering is working.

                      I would greatly appreciate it if you could suggest a way that is possible for my database to filter through my 11 description related fields.

                      For instance the fields that should be used for creating this configuration are:

                      [Size][hose name][hose accessories][left fitting series][left fitting type][material fitting][left fiting accessories][left crimp ferrule]

                      [right fitting series][right fitting type][right fitting accessories][right crimp ferrule][clamp ferrule material].

                      These fields have all been entered into one table called DATA ENTRY they are related to another table by SIZE.They all have values however there always seems to be a problem after the left fitting type selection. It doesn't filter my records for material fitting it shows all values and they are not related to previous selection. I have selected show only related values for all fields with the exception of size which saya show all values.

                      For example:

                      If i were to select a menu and i said breakfast i should only see the selections for breakfast, instead i see al loptions for breakfast lunch and dinner.

                      It is only with this field and its options that i have difficulty in filtering, as you move past that field the left accessories etc all filter perfectly.

                      Thanks in advance fo any help you can provide

                      Jess