1 2 Previous Next 18 Replies Latest reply on Nov 21, 2015 9:01 AM by LesAteliersTurcotte

    Simple scripts / formulas Help

    LesAteliersTurcotte

      Hello all,

       

      I'd be extremely grateful if someone would show me how to do the following.

       

      We have imported a database from Bento and I am trying to make it more user-friendly for Filemaker.

       

      1) We used to have a field that showed a quality of the product. This was either 0 (no it does not have the quality) or 1 (yes it does). How do I conver the 1s and 0s to Yes and NO? I am so sure this is a simple calculation, I just am not able to find it.

      I tried an IF function, but it says: "The specific field cannot be found"

      This is the function I tried (using another field as the destination): If ( Reflective=1; YES  ; NO )

       

      2) How can I create an editable database? Because we did a transfer from BENTO and a lot of things were organized differently, although we preserved the info (which is great!), I had to organize it a bit differently. For instance (someone helped me previously with this), I created a list of colours that an item had, instead of showing all the colours listed with 1 and 0s next to them (1=yes, 0=no). Just to clarify, we have an inventory of clothes. A skirt can have more than 1 colour.

      So right now, I have a field showing a sum of all the colours previously listed as 1s or 0s, but I cannot edit this. I cannot add another colour in the field (evidently! since it's a calculation based on another information), I cannot edit those colours.

       

      Is there a way to have a 2-way system? So I can edit this interface and the results would be changed in the original 1s and 0s imported?

       

      Thank you SO much.

        • 1. Re: Simple scripts / formulas Help
          jbrown

          Morning.

          You can use the "Replace Field Contents" action in the Records menu to accomplish #1.

          1) We used to have a field that showed a quality of the product. This was either 0 (no it does not have the quality) or 1 (yes it does). How do I conver the 1s and 0s to Yes and NO? I am so sure this is a simple calculation, I just am not able to find it.

          Simply show all your records. Then click into that field. Go to Records / Replace field contents menu and type in the following calculation (the last choice in that dialog)

           

          If (YourTable::YourField = 1; "Yes" ; "No")

           

          Then press enter. All the 1s will be turned to "Yes", and the 0s to "no".

          • 2. Re: Simple scripts / formulas Help
            jbrown

            To accomplish #2, you'd need to get the concept of value lists in FileMaker, and how a field in a table can be used in a value list.

            I'd create a table called Colors with a field "colors". Fill that table with the colors of your choosing.  THEN on your "Products" layout, create a field that uses this value list as check boxes. The user can then select multiple colors per item.

             

            The only problem with this is that if you delete, say "Red" from your color table, you will not see "red" in the list for those products that had that color checked previously. But this is a start, I think.

            1 of 1 people found this helpful
            • 3. Re: Simple scripts / formulas Help
              LesAteliersTurcotte

              Hi Jeremy,

               

              I have just done this, thank you.

               

              The only issue I have now is that if we ever change the YES/NO field, it will NOT automatically update the 0s and 1s. Which is confusing: can I then download the data without the formula applying? I mean the Yes/No field is now the result of a formula, right? So if we change this result, shouldn't the calculation also change and give 0?

              Do you see my dilemma?

              • 4. Re: Simple scripts / formulas Help
                LesAteliersTurcotte

                I have also just checked: even if I change the original 1-0 field, the YES and NO does not change. Which is strange: is therefore the calculation only made at the beginning of the field change, and never afterwards? If it's a direct relationship, then why aren't the YESes and NOs changing once I update the origin field?

                • 5. Re: Simple scripts / formulas Help
                  jbrown

                  I'm not fully sure I follow.

                  The Replace Field contents is just an action. So it will not affect any other data that is set.

                   

                  You could set the field to an auto-enter calc to CHANGE an imported 1 to a Yes

                   

                  Something like

                  Case (

                  Self = 1; "Yes";

                  Self = 0 ; "No";

                  Self )

                   

                  If you set this as the auto-enter calc, then this will get run each time this field is set when you create a new record or import.

                   

                  Maybe I don't quite get what you're saying   let me know.

                  • 6. Re: Simple scripts / formulas Help
                    jbrown

                    I forgot a basic thing. Make sure the field is now a text field. A 1 or 0 can live in a text field, but a "Yes" might have trouble in a number field.

                     

                    I guess I'd just create another field that is used. Call this

                    Quality_Text

                     

                    Then run the Replace field contents on this field referencing the other field.  BTW: Make sure to select the field (or type the whole field name) so that FM knows which field you're looking for.

                    • 7. Re: Simple scripts / formulas Help
                      LesAteliersTurcotte

                      Yes. I had created a Text field.

                       

                      So I have the following fields:

                       

                      - Reflective : shows as 0-1 based on previous import - number field

                      - Reflective YES/NO: replaced calculation with: If ( Costume Program Converted 1::Reflective ; "Yes" ; "No" ) - text field

                       

                      This is all very well.

                       

                      My previous question was: If I change the YES NO field, let's say for item 13. So Item 13 was NON-reflective, but upon further consideration, I decide it is. I change the NO automatically filled to "YES". All good.

                       

                      This means that:

                       

                      When I download the data, I should only refer to the new "Reflective  YES/NO" field as this will be the one updated, right? The Reflective (original) will still show as 0, which is now incorrect.

                      Will this info be ever obliterated by the fact that I have a formula in the field, therefore (if it were Excel, for instance), it would automatically change the outcome. So for instance if in Excel I would have a formula such as IF, and change the result box, it is possible to create a double-sided relationship so that even my original data (0-1) is changed.

                       

                      Do you see what I mean?

                      • 8. Re: Simple scripts / formulas Help
                        LesAteliersTurcotte

                        I should specify that I am now only using the replace function as you told me to - but originally I wanted to create a field with an "auto-calculation".

                        • 9. Re: Simple scripts / formulas Help
                          jbrown

                          You have two fields:

                          Reflective (number field) with 1s and 0s from Bento

                          Reflective _Yes/No ( text field). Yes or No depending on what was in the Reflective field.

                           

                          Moving forward:

                          1. From now on you should just use the Reflective_Yes/No field. As you state, this will be correct, and the previous field will be incorrect.

                          2. Since the 2nd field is simply a text field, nothing will happen to the older field. In fact, I'd consider getting rid of this Reflective field. Why keep it? There's no value in it anymore. You want the user to choose "Yes" or "No" for the reflective attribute. You imported the data from Bento as 1s and 0s. You converted them to "Yes" and "No" respectively.

                          • 10. Re: Simple scripts / formulas Help
                            LesAteliersTurcotte

                            I have one other question:

                             

                            I have 4 picture fields. When I click on one item, the picture present there is always the 4th picture (which is often empty, bc we usually have 2-3 pics max per item). Is there a way to change this so that the first container/pic field is the one filemaker automatically opens when I select an item?

                             

                            I've attached print screens so it's a bit more clear.

                            This is what I want:

                            Screen Shot 2015-11-21 at 11.11.35 AM.png

                            This is what's happening now:

                            Screen Shot 2015-11-21 at 11.11.40 AM.png

                            • 11. Re: Simple scripts / formulas Help
                              jbrown

                              I haven’t tried, this but I wonder if FM is confused about which should be the default tab since they’re all named the same.

                               

                              In the tab-setup dialog, I’d name each one different and then Choose “Picture 1” as the default tab.

                              EDIT: after some testing, the tabs only get reset to the Default Front tab when a user goes to the layout. Navigating between records doesn't reset the tab. You'd need to create an onRecordLoad script trigger that goes to the object. In my test, I named the first panel "One" after clicking on the first tab and naming it in the inspector (position ). Then I wrote a trigger script to go to object ["One"].

                               

                              You may want to consider, instead of four picture fields, that you create another table of pictures and use a portal in that area to show the pictures. This approach saves you the trouble of having the tabs for empty pictures. If there is no 4th pic, don’t show anything that might hint that there’s a 4th pic

                               

                              Here’s a post by a colleague about this topic: https://www.youtube.com/watch?v=wuDhakEOQeE

                              • 12. Re: Simple scripts / formulas Help
                                LesAteliersTurcotte

                                Jeremy Brown wrote:

                                 

                                To accomplish #2, you'd need to get the concept of value lists in FileMaker, and how a field in a table can be used in a value list.

                                I'd create a table called Colors with a field "colors". Fill that table with the colors of your choosing.  THEN on your "Products" layout, create a field that uses this value list as check boxes. The user can then select multiple colors per item.

                                 

                                The only problem with this is that if you delete, say "Red" from your color table, you will not see "red" in the list for those products that had that color checked previously. But this is a start, I think.

                                Ummm, how do I set a check box?

                                I have created a table Colours, with a field Colours. This I have linked to my original program/table. All good.

                                 

                                Now in this field, I entered 4 colours.

                                 

                                How do I make them checkboxes?

                                 

                                Sorry for all the questions!

                                • 13. Re: Simple scripts / formulas Help
                                  jbrown

                                  No worries about all the questions. Its how you learn☺

                                   

                                  TO make a field checkboxes, you need to use the Inspector. IN the Data tab, there's a dropdown for a field control style. Choose "Checkbox" for that field. All the values in the value list will then have checkboxes by them.  A user can check or uncheck as many as is needed.

                                  The data stored in the filed will be a return-delimited list of the values they chose IN THE ORDER in which they chose them.

                                  Make sure this field has a value list attached that will display the colors from your colors table.

                                  • 14. Re: Simple scripts / formulas Help
                                    LesAteliersTurcotte

                                    Wow this is amazing! Lol thank you so much

                                    1 2 Previous Next