1 2 Previous Next 26 Replies Latest reply on Dec 8, 2010 11:26 AM by DavidCampbell

    Case statement or script?

    DavidCampbell

      Title

      Case statement or script?

      Post

      Hiya,

      I'm trying to do the below and it's not working. Any assistance would be very much appreciated:

      Case (TableA::ThisYear = "Yes" and not IsEmpty ( TableA::Field1 ) and not IsEmpty ( TableB::Field2 ) and TableA::Field1 = TableB::Field2;TableA::Field1;
      TableA::ThisYear = "Yes" and not IsEmpty ( TableA::Field1 ) and not IsEmpty ( TableB::Field2 ) and TableA::Field1 ≠ TableB::Field2; TableA::Field1 & " " & TableB::Field2)

      The fields contain only text and are coming from portals in two layouts which I need to combine in a third portal/layout.

        • 1. Re: Case statement or script?
          philmodjunk

          We need more info.

          What is the table occurrence context? (On what layout is this being evaluated, TableA? TableB? a Third table?

          What are the relationships that link these table occurrences?

          (A table occurrence is one of the "boxes" on your relationship graph. Each layout and each portal refers to one such occurrence in the "show records from" drop down found in Layout Setup... and Portal Setup...)

          • 2. Re: Case statement or script?
            DavidCampbell

            Layout 1

            TableA
            ::ThisYear [Yes No]
            ::Field1

            Layout 2

            TableB
            ::ThisYear [Yes No]
            ::Field2

            Layout 3

            Field in TableC is the one I'm trying to fill with data using Case from the portals in layouts 1 & 2 above.

            The problem is I choose Yes in Layout 1 and select data for Field1 but nothing shows up in the field in layout 3. Also, when I choose Yes in both layouts 1 & 2 and fill in data for Fields 1&2, nothing shows in the field in layout 3. I just realized I also don't have a case to show data if the conditions are met in only layout 1 or layout 2... crap.

            Does that make sense?

            • 3. Re: Case statement or script?
              philmodjunk

              What do you have in Manage | Database | Relationships?

              If you are trying to assign values to a record in TableC you can't refer directly to any fields in TableA or TableB unless you've defined a relationship linking them to TableC. Without the relationship, FileMaker doesn't know which record in TableA nor which record in TableB to refer to in order to use that data in your calculation.

              • 4. Re: Case statement or script?
                DavidCampbell

                I've inherited this db and it has 23 tables in it. Looking at the Relationships between the tables:

                Tables A&B are related to TableC through another table, let's call it TableD. All three A,B & C have a foreign key in common all tied together through a single primary key.

                I get no errors and I use the available fields in the calculation dialog window to select the fields in related tables, etc. (in other words I'm not typing in things).

                • 5. Re: Case statement or script?
                  philmodjunk

                  And is there a "TableD" record that matches to the desired record in table A and also to the current record in Table C? Without such a record, there is no data accessible from TableA at the time your calculation evaluates. Also, you could have more than one record in TableA that matches to the current record in TableC. In those cases, this calculation will refer to the "first" related TableA record which could be a different record than you expected.

                  I'm just pointing out possible explanations. I don't have a detailed enough description of your relationships to say much more than that.

                  Things you might consider:

                  Create a relationship that directly links Tables A and B to table C. This may require creating new table occurrences of tables A and B. (Don't make a new table occurrence of Table C.)

                  Use a script that saves the desired values in variables and then uses set field to assign them to fields of a record in Table C. This can avoid the entire relationship issue here and just work from current records on each layout.

                  • 6. Re: Case statement or script?
                    DavidCampbell

                    Thanks a lot for your help. It sounds like my best option is to try it via a script as you mentioned. Users will enter the data in layouts 1 & 2 and then save it before going to layout 3 so the data will/should be available for the calculations in layout 3 when they get there (and that field will not be changeable manually). Where should I start to build a script that might help with what I need to do? I've done the Lynda.com training but most if it is for very simple things....

                    Thanks again for your time.

                    • 7. Re: Case statement or script?
                      philmodjunk

                      With calculation or script you have to answer one key question: How does the database "know" which record in table A and which record in table B is the correct record to refer to?

                      If it's the current record like I think it is, you could write a script that is performed from a button on your table C layout that starts out like this:

                      Freeze Window
                      Go To Layout [TableA]
                      Set Variable [$ThisYear ; Value: TableA::ThisYear = "Yes"]
                      Set Variable [$Field1 ; Value: TableA::Field1]
                      Go To Layout [TableB]
                      Set Variable [$Field2 ; Value: TableB::Field2]
                      Go To Layout [original layout]

                      I'm not sure what you want to do next. If you want to assign a value to the current record of TableC, you can use:

                      Set Field [TableC::YourFieldHere ; Case ( $ThisYear and not IsEmpty ( $Field1 ) and not IsEmpty ( $Field2 ) and $Field1 = $Field2 ; $Field1 ;
                      $ThisYear and not IsEmpty ( $Field1 ) and not IsEmpty ( $Field2 ) and $Field1 ≠ $Field2 ; $Field1 & " " & $Field2 ) ]

                      If you want to create a new record in TableC and then save this result, you'd put New Record/Request just before this set field step.

                      • 8. Re: Case statement or script?
                        DavidCampbell

                        Interesting... re your initial question, are you asking that because it's a portal (not real sure how those work exactly)? In my initial explanation I replaced my field and table names with generic ones but ones that are distinct:

                        Layout 1

                        TableA
                        ::ThisYear [Yes No]
                        ::Field1

                        Layout 2

                        TableB
                        ::ThisYear [Yes No]
                        ::Field2

                        Both Field1 & Field2 already exist in TablesA & B, respectively, and if a user enters data for them they will have data in them to use in the field I have in TableC. 

                        So the fields in TableA & B are in FM notation: TableA::Field1 and TableB::Field2. The layouts already have other fields showing data in them from tables, maybe I should look a bit more at them... none are doing anything close to what I'm trying to do though (most are very simple calculations)... the back story is that the db was created using 13 FM5 dbs and the interface/layouts were made, some of the data was imported and then the consultant basically died (figuratively), leaving me to pick up the pieces. I have fixed almost everything else (minus data validation from the old source) except for this aspect. My background is in websites mainly so I understand dbs in simple terms but have mostly scripted in asp, php and javascript in only limited ways (mostly customizing existing ones).

                        • 9. Re: Case statement or script?
                          philmodjunk

                          If you have a portal on any of these layouts, please note that this changes things. And yes you will need to learn how portals work. Portals are windows into related sets of records.

                          I should have picked up on that earlier as you did mention portals.

                          Is TableA, TableB and/or TableC a reference to the records in a portal or the layout? In layout setup... and portal setup... there are Show Records From drop down menus. We need to establish what is listed in these drop downs for each portal and each layout before we try anything else.

                          Note that  if you see ::ThisYear on a layout, FileMaker is showing you that this field is not part of the layout's table. The :: indicates that this would be a field from a related record. You'd have to double click this field to find the name of its table occurrence and then look for the relationship linking the layout's table occurrence to the field's table occurrence in Manage | Database | Fields to learn how they are linked in this relationship.

                           

                          • 10. Re: Case statement or script?
                            DavidCampbell

                            Great. Yes I have portals on all three layouts. In more detail then:

                            Layout 1 (tabbed portal): portal contains related fields only from TableA, fields outside portal are from TableD (the one common to TablesA, B & C) 

                            Layout 2 (tabbed portal): portal contains related fields only from TableB, fields outside portal are from TableD (the one common to TablesA, B & C) 

                            Layout 3 (tabbed portal): portal contains related fields only from TableC, fields outside portal are from TableD (the one common to TablesA, B & C) 

                            So I have a field in layout 3 which exists in TableC that I would like to populate with data from an exiting field in each portal in layouts 1&2 IF Yes is selected for each record and data exists in the existing fields in question in the two portals.

                            Visually:

                            ~~~~~~~~~~~~~~
                            Layout 1

                            ....TableD fields...
                            _____portal_______

                            TableA::Field1   TableA::FieldA  TableA::FieldB  TableA::ThisYear [Yes No]
                            ...rows

                            _____portal_______
                            ....TableD fields...

                            ~~~~~~~~~~~~~~

                            ~~~~~~~~~~~~~~
                            Layout 2

                            ....TableD fields...
                            _____portal_______

                            TableB::Field2   TableB::FieldX  TableB::FieldY  TableB::ThisYear [Yes No]
                            ...rows

                            _____portal_______
                            ....TableD fields...

                            ~~~~~~~~~~~~~~

                            ~~~~~~~~~~~~~~
                            Layout 3

                            ....TableD fields...
                            _____portal_______

                            TableC::Field1   TableC::FieldK  TableC::FieldL...
                            ...rows

                            _____portal_______
                            ....TableD fields...

                            ~~~~~~~~~~~~~~

                            So all my layouts are part of TableD because all data outside their portals is from TableD (TableD is the largest and central table of all).

                            I need TableC::Field1 (above) to show data under certain conditions from TableA::Field1 and/or TableB::Field2 (above) IF either of their ::ThisYear values are Yes.

                            I can create and modify portals, if there's more to know about them let me know... I'll google a bit now in fact.

                            I hope that clarifies things better. Sometimes I try to simplify things to make things easier on others and go too far.... Will look forward to your reply.

                            • 11. Re: Case statement or script?
                              philmodjunk

                              Do all three layouts show table D in Layout Setup... | Show records from ?

                              You can put related records directly on the layout so that doesn't guarantee that the layouts are based on Table D.

                              • 12. Re: Case statement or script?
                                DavidCampbell

                                Yes, in fact all fields outside the portals on the layouts are exactly the same ones from TableD as well.

                                • 13. Re: Case statement or script?
                                  DavidCampbell

                                  In other words, it's the same layout with tabs. One tab (that I called Layout 1 oops) has the first portal in it, a second tab has the next one in it and what I called Layout 3 is actually tab 3 with the third portal in it. Sorry about that, still got a lot to learn re the terms.

                                  • 14. Re: Case statement or script?
                                    philmodjunk

                                    So we have

                                    TableD ----< TableA

                                    TableD---<TableB

                                    TableD----<TableC

                                    Since you have portals, how does a portal row in the portal to TableA match up to a portal row in TableC? IF we are going to write a script that collects data from the portals to TableA and TableB, we need some way to know which row in the portal is the once to use.

                                    I'll google a bit now in fact.

                                    Instead of Google, first select FileMaker Pro Help from the help menu and search for info on Portals there. Wink

                                    1 2 Previous Next