5 Replies Latest reply on Sep 3, 2013 10:39 AM by philmodjunk

    text concatenation not working in related fields AND storing calculated result

    JennySemmler

      Title

      text concatenation not working in related fields AND storing calculated result

      Post

           Hello folks, I'm having trouble with text concatenation.

           I have Patch and Component tables in a one to many relationship through the PatchID field (number). The Patch table is related to the Vineyard table through the Vineyard ID (text) and looks up the Variety through the variety ID (text). 

           The component table contains fields Vintage (number), batch number (number) and type (text)

           I am trying to concatenate text on the Component form through the Component:Component Code field - as an unstored calculation, calculated as text.

           Problem 1: ideally I would like to store the result of this to use in a dynamic dropdown which only shows current components (thinking to self - how to tell when component is no longer current?)

           Component code=Ptach:VineyardID & Component:Vintage & Component:type & Variety:VarietyID & Compoentn:Batchnumber & Patch:Farming method

           I select the patch on the component layout with a drop-down field from Patch:Patch code using a manually assigned patch code (the patchID is just an autonum for indexing). The output should look something like SEM13RSHZ1O (Semmler, 2013, red, Shiraz, batch 1, organic).

           Problem 2: Instead, my output is 2013R1 ie it is only picking up fields from the component layout, regardless of whether they are text or numbers. 

           I originally thought it may have been my relationships which go like this

           1 Vineyard -> many patchs   

           1 patch ->many components

           select a component through a dropdown on calculated field Component:Component Code to use the component code to build

           1 blend -> many components

           I have tried: 

           1. altering the relationships to component:ComponentID and Patch: componentID (which violates unique values in component:patch)

           2. making this a dual criteria join (match Patch ID and component ID) (which violates unique values in component:patch)

           3. using getastext within the calculation for any number (no difference to outcome)

           4. placing lookup fields in the component table for vineyard, variety and farming method (text does not auto-enter from the related tables).

           This suggests my relationships are wrong. Can you please advise whether I require:

           a. a self-join, and if so which table/s

           b. an indexing table eg patchID and componentID fields only, sitting between patch and component fields

           I'm sorry this is long-winded, however I have spent hours trying to resolve this one.

           Any help you can give would be greatly appreciated. Thank you in advance.

        • 1. Re: text concatenation not working in related fields AND storing calculated result
          philmodjunk

               You appear to have these relationships:

               Vineyard----<Patch----<Component
                                           v
                                            |
                                        Variety

               Problem 1. An auto-entered calculation can concatenate these values and store them in a text field. Script Triggers on each field referenced from a different table occurrence can perform a script to find the affected Component records and update them. But, an unstored calculation field can be used as a source of data for a value list, provided that you have a second, indexed field which you specify as the sorted field in the value list. That may or may not work for you.

               Problem 2: Sounds like you have a problem with your relationships. They might not be set up with the correct match fields, your drop down may be assigning a value to the wrong field or the data in the designated match fields may not actually match as expected. To check that, try adding each of the fields referenced in the relationships to your Components layout. If your relationships are valid, you'll see those fields correctly displaying data when you select a patch from the value list.

          • 2. Re: text concatenation not working in related fields AND storing calculated result
            JennySemmler

                 Thanks for your thoughts Phil.

                 My relationships are actually Vineyard (1) ----> Patch (Many)   ----> Component (Many)

                                                                                                           | Varieties

                  

                  

                 I had originally made a component form and tried to concatenate on that with a patch portal, and tried putting the related fields from Patch & Vineyard onto the component form to test the relationship, with no luck. I do believe my relationships are right--- I have simply put the components portal on the patch form and Voila! it works!

                  

                 PROBLEM SOLVED THANK YOU!  - NEW Q

                 So my next question is then, I now have components that I want to blend together. In my problem version above I made the component from the patch on the blend layout via a portal Blend (form) ->component (portal) which worked OK-ish. So how can I add a component to a blend after the component has been made? 

                 I imagine a "get component" button in the portal with a script to take you to the component layout as a form so you can select. I would need a step in there to conduct a "find component" and set value (component:Blend No = Blend:Blend No).

                 How can I ask for input on the find parameters on the concatenated field above (component code) which is calculated each time required? I don't know how to pause the script and ask for find input parameter and to store the concatenated field for searching.

                 I'll have a fool around over the weekend and see if I can work it out, but I'm a little out of my depth.

                 (PS in Bento it was pretty easy - you pressed the "add related record" button then did a search in the search bar, add). 

            • 3. Re: text concatenation not working in related fields AND storing calculated result
              philmodjunk

                   My relationships are actually..

                   You seem to have repeated what I posted there. I dont' see any difference. (---< means "one to many" in my notation )

                   To that you have now added a Blend table: Component>------Blend

                   And that is a relationship that will work for assigning component records to a common Blend record. But are you sure that this will be a many to one relationship? Could the same component be part of more than one Blend? If so, you have a many to many relationship and you need to change your relationships in order to make that work.

                   Assuming that it is indeed a many to one relationship...

                   

                        how can I add a component to a blend after the component has been made?

                   There are multiple methods you can use for selecting a Blend record. You could, for example, place a global field on your layout and set it up with a value list of Blends. Selecting a value in the value list can trip a script trigger that then finds that record and update's it's blend no field to that of the current record on your blend table.

              • 4. Re: text concatenation not working in related fields AND storing calculated result
                JennySemmler

                     Thank you Phil, I didn't understand your notation - so it seems I'm on track. 

                     I have solved the blend building method - I have built a butotn/script to bring up new window for the Component layout (list) for a search, and manually assign the blend number with a "select component" button. For now this will do, although as I get closer to finishing the database build I'll see if I can write a script to automatically assign Component:Blend No = Blend:BlendNo to the Select button (I've fooled around with the SetValue function but for now I'll leave it). I'm not advanced enough to develop a pick list for a global value, and this way I can filter out components that have been consumed (I anticipate I'll have thousands of components within a year or two). 

                     WRT to the relationship component>----Blend and Blend>----Component,  I believe I need a selfjoin on Component, so the linear relationship will be

                     Patch-----<Component=Component2>-----Blend

                     I think I should be right for now, thank you Phil. I will ask again if I get stuck, but I feel as if I'm on track. 

                     On another matter, if you are working for Filemaker - you *are* working very hard, and I appreciate your time for me. If you are a private person, even more so! 

                      

                      

                • 5. Re: text concatenation not working in related fields AND storing calculated result
                  philmodjunk

                       People employed by FileMaker that post here use a forum name starting with "TS" for technical support. The one exception is modman-a manager at FIleMaker Inc. that manages/moderates this forum.