14 Replies Latest reply on Jan 11, 2016 4:08 PM by bigtom

    Need to solve a problem involving value lists and serial keys


      Hello everyone,

      Many thanks to the people who have helped me in the past - I'm learning and growing as a filemaker developer every day thanks to your help.


      There's a set of fields on one of my layouts at work that I'm having an issue with.

      Before I was hired and started learning filemaker myself, the owners hired a professional filemaker dev to help them code this ink section of our database. I'm sure that what he did is very clever and functional, but for our purposes it just doesn't work quite right.


      I've attached a few screenshots that I will be referencing as I type this.

      You'll notice that in the first screen shot there is a series of fields that are labeled "INK". These are drop down menus pulling from a value list that the developer created. I've attached a screen shot of said value list so you can see what he did and hopefully make sense of it, since I can't.


      I need to change this value list, but unfortunately that retroactively changes the contents of that field to a serial number that my guys on the floor can't use. Essentially making all of our records of ink before the change useless. I've attached a screenshot of the same area of our DB (same record) with the value list changed so that you can see this in action.


      Here are my ideas for solutions to this, they may not be great ideas but I don't know how to make them happen, regardless.

      1. Somehow convert all of these serial number to their text-based name across all of the records they appear in. Somehow the serial numbers are being converted to text based temporarily - I'd like to convert them permanently
      2. Add a button / function that allows the user to change the value list that the field is pulling from. This would kinda suck but bandaid fix it until these serial numbers are far enough behind us that the button function becomes obsolete.


      Thank you all in advance. Excited to solve this problem with your help.



        • 1. Re: Need to solve a problem involving value lists and serial keys

          Your main layout table stores a foreign key to Danger_ink table.

          If you want to change this to reference some other table ( not just another field from same table ) then obviously it will brake and if there's no match it will show what actually is stored in the field.


          Maybe try explaining what you want to change the row INK it to?

          • 2. Re: Need to solve a problem involving value lists and serial keys


            So we're screen printers, the screen shot I attached before is just a small portion of the layout that our managers use to communicate info to the guys on the floor. I've attached a screenshot of the full layout.


            We have a separate table that stores ink formulas that are made in house. Each ink has a variety of variables attached to it - what it's "based" on (Plastisol or Water), What System was used to create it (Wilflex, Union, PMS) and what medium it's used for (Posters or Apparel). What I've done is created a value list that takes all of these into consideration so that whn you click an "ink" field, it only shows you inks that are a match to the job you're working on. For instance, if "WORK TYPE" (Up that the top center-ish of the layout) says "Apparel" and the "Ink type" under head 8 says "Plastisol" when you click on "Ink" it's only going to give you Plastisol Apparel Inks. Does that make any sense at all?


            This functionality works perfectly. The problem is that I don't want the old serial number system interfering with my new, cleaner and dynamic system. The old system simply listed EVERY single ink in the database... hundreds and hundreds of inks.


            What I want to do is use my new system, but not lose our back catalog of jobs, because we reference them frequently. Usually filemaker doesn't retroactively change what shows in fields across the DB, but in this case it is. Which is no good.


            Does that help?

            • 3. Re: Need to solve a problem involving value lists and serial keys

              What exactly do you need to change in the value list? Any changes should be made in the actual Danger_ink table.


              I think I see what you are getting at about the display of the serial number where you want to see the actual color name.


              Do not make any changes to the way anything was setup before. This may cause more issues later.


              This is a slightly tricky thing in FM because of the way things work. The fields on the layout for viewing the data should be changed from an edit box to a PopUp menu using values from the Danger_inks value list. Then format the field to not display the outlines or pulldown arrow (hide it with a blank/null color). Then do not allow data entry in Browse mode. All of this is done using the inspector. This will give you the color name instead of the serial number. This is simply about manipulating which data is displayed.


              If you prefer a specific field you can use ExecuteSQL to grab the color name into a new field.

              • 4. Re: Need to solve a problem involving value lists and serial keys

                I need to change the value list because the system using the serial numbers is now obsolete. We're moving to a new table, with a new value list, etc.


                Would you mind expanding on the Execute SQL option? If I can grab JUST the color name and place it into a new field, that may solve my problem.

                • 5. Re: Need to solve a problem involving value lists and serial keys

                  I see.

                  I don't know how much you reworked your solution to adapt the new model.

                  What you're using is most likely a conditional value list for INK starting from WORK TYPE. Am I right?


                  The big question: will the same table be used for the inks: Danger_Ink?


                  Aside from the fact that the field needs to be defined as a pop-up and the value list defined as the original one.


                  One possible error is that you conditional value list is not working correctly.

                  Can you actually select any values if you click into the popup?

                  • 6. Re: Need to solve a problem involving value lists and serial keys

                    You can find any number of examples on how this works, but the basics is that it goes to any table and get data based on a query. The tables do not need to be related. As a rough example:


                    ExecuteSQL( SELECT Color FROM Danger_ink WHERE __kp_ink =?; ""; ""; SerialNumberValue)


                    This may require a Let statement to fill in the Serial number value on the fly or you may want to just set a value by script and loop through everything once to set the color names.


                    If you simply want to change the display use the previous suggestion.

                    • 7. Re: Need to solve a problem involving value lists and serial keys

                      Or you may consider displaying both the serial number and the color name to keep backwards compatibility. Not sure how everything works.


                      How did the serial number end up in the new table but not the color name?

                      • 8. Re: Need to solve a problem involving value lists and serial keys

                        A new ink table?

                        For the sake of getting the current color data you can create a new field "Ink Color" in the primary table ( where the serial is stored ). Set it up to lookup the data from Danger_Ink color field.

                        Then ( best done in a table view layout ) and all records showing, go to the serial number field and use Relookup Field Contents from the Records menu.

                        This will auto populate the values.


                        But this will de-normalize your data. If it's only to provide a workaround for displaying old data then I don't think it's a great idea.

                        It could make your life as a developer a nightmare and create confusion for the user.


                        The thing is INK is already an unique entity. There should be no two the same.

                        In your situation I'd do everything I can to match the old data to the new, preserving the serial numbers in both tables.


                        So I'd come up with a data migration plan.

                        • 9. Re: Need to solve a problem involving value lists and serial keys

                          Electon - You're close.

                          It gets a little messy, but I'll try to explain as best I can:


                          When you create a new ink in our ink database (screenshot attached) you're prompted for multiple pieces of information. One of which is Medium ( = Work Type) and another is Ink Type ( = Type). Each "Head", or column, on the screen labeled "PREPRESS" is calling a value list specific to it's column. So it's related on "TYPE(of ink)" and "WORK TYPE(medium)". It's then giving you a conditional value list starting from an the ink database table, related to the "Work Type" and a specific "Type" field.


                          Although this probably isn't the pro way of getting this done, I've been working with filemaker for about 6 months and I'm pretty proud of this functionality. Now if I could just implement it without rendering our older jobs (3,000+) useless...

                          • 10. Re: Need to solve a problem involving value lists and serial keys

                            BigTom - I'm going to look into this solution. Thanks for your help.


                            The guy who coded this thing before I got my hands on it did that serial key table. I have no idea what it's used for or what good it does - it basically has just caused a ton of problems and he clearly coded it in a way that an amateur such as myself could never even begint to understand whats happening without a few more years under my belt.


                            My inkling is that since every ink is unique, its name is enough of a serial number for our purposes. Obviously he thought differently.

                            I guess that's what happens when you hire someone to build industry specific software for an industry he's unfamiliar with.

                            • 11. Re: Need to solve a problem involving value lists and serial keys

                              First: A serial number is what identifies unique records in relations and should not ever change!

                              So don't blame the developer, he did the right thing. A name as you suggest is not a serial number.


                              You need to carefully think about your data model here:

                              1) Every ink is unique, right?

                              2) Can only one ink belong to only one System or Apparel or Type?, or can one ink be assigned to many combinations of those?

                                   this is really important.


                              I'm beginning to think that what you need to do is redesign your current Danger_Ink table to adopt the needs of the new model.

                              So all the options should go there. This way you won't break the relationships.


                              And with all respect to bigtom. It's definitely not the correct answer, I'm afraid.

                              • 12. Re: Need to solve a problem involving value lists and serial keys

                                I understand where you're coming from, and I apologize for misusing the word Serial Number, but I genuinely think that BigTom has a better understanding of what I'm trying to accomplish.


                                The Danger_inks table is obsolete. I do not need it, I don't want to use it, I wish it would disappear and have never existed in the first place. It has been nothing but trouble and no one here even knows how it functions, much less how to work on it. Regardless, all of that is moot because it didn't even work the way it was designed to work. So what I did is, from the ground up, COMPLETELY rebuilt our ink system. New tables, new records of each ink, new everything. And it works perfectly. The ONLY thing I need to be able to do now is keep our old records in-tact by making the ink field display text instead of serial numbers. I don't want to add anything to the Danger_ink table, it has hundreds and hundreds of unuable, obsolete, or confusing records attached to it.

                                • 13. Re: Need to solve a problem involving value lists and serial keys

                                  I understand that your table may be obsolete but I think you don't / want to understand how a relational database works, and how easily thinks can break if you don't have the proper understanding.

                                  It's your party and I can't force you but take this scenario into account:


                                  You create a new INK with a name Z345 Blacck, notice the typo!

                                  Then someone else creates a few jobs with that ink and later on you or your client discovers that there's a typo.

                                  You then go and change the name to Z345 Black in the INK table and your relationship breaks.


                                  You won't be able to search for jobs that used that ink, that's for one.

                                  If you want to do any kind of reporting by INK that has jobs, your data will be incorrect.

                                  Etc, etc...

                                  Unless you go back to jobs and change it to exactly the new name you give the INK ( every instance where it's already used ).

                                  Well , good luck with that and I my hat off to your confidence level you will need to have to mess with existing data.


                                  They invented serial numbers for a reason, you know.

                                  • 14. Re: Need to solve a problem involving value lists and serial keys

                                    I also advise you using some sort of record key or serial number for the same reasons that @electon has described. It is easy for things to break.