6 Replies Latest reply on Jan 11, 2011 10:29 AM by philmodjunk

    Finding matching records in two tables

    bookalaka_1

      Title

      Finding matching records in two tables

      Post

      I have an inventory list with "specials"  on certain items one for retail and one for restaurants the descriptions can  be somewhat lengthy so I have scripted a separate window  one for retail and one for restaurant that I can open and do a search. Ideally what I want is to define a script that will "find" the items in the floating windows by matching the item code.  Since not every item has a special I have started by  creating a field "IsEmpty_Retail" and IsEmpty_Restaurant this way I can see if there is text in the description field. Ideally I would like to attach a button that when clicked will open and find the data in the other tables.  Where Im getting a little lost is how to find the matching data in the Restaurant and Retail Tables and not have multiple windows open each time I click.

      Thanks for the input.

        • 1. Re: Finding matching records in two tables
          philmodjunk

          I think you want to use a script something like this, but I could be wrong as I'm not sure I understand your request fully:

          Set Variable [$Item ; Value:  YourTable::YourMatchField1]
          New Window [// specify a window for displaying the results of your search]
          Go To layout [//select layout to other table]
          enter Find mode[]
          Set Field [Your2ndTable::YourMatchField2 ; $Item]
          Set Error Capture [on] // keeps error dialog from interrupting script if no records are found
          Perform Find[]

          This opens a window and uses data from the current record (or a record linked via a relationship to it) to perfom a find on a layout displayed in a new window the script opens for you.

          • 2. Re: Finding matching records in two tables
            bookalaka_1

            Hi Phil,

            I didn't quite get the set variable solution so here is what I ended up doing. although this works my window commands are a bit of a problem. I need to clarify  to open a new window and if not open and once the new window is open not to open a second and third. Also I was going to attach a picture but couldn't figure out how to send? and how can I copy a script from the script manager and paste into the forum dialogue? Also why I wanted to send the picture is for the "IsEmpty function" currently I have a box that shows either the 0 or 1 that I have my script attached to. I know if I see a 0 there is special information on the product. How can I make this better for a user so instead of looking for a 0 they would see 0= "Special", 1 = Not Available?

            lots of questions

            Thanks Again

            My script

            go to field[select/perf:inv_items::codecalc_pk]

            select window"name:"restaurant" current file

            go to field[unp_rest_iitems::code_pb_pk]

            paste[select:up_rest_item::code_pb_pk]

            enter finde mode

            perform script[paste[select:up_rest_item::code_pb_pk]

            perform find

            • 3. Re: Finding matching records in two tables
              philmodjunk

              Copy and Paste are not good options to use. They require that the referenced field be physically present on the layout that is current at the time that step executes. If you accidentally remove that field at some point in the future, the script silently fails to copy or paste the data. It also destroys any data the user may have copied to the clipboard and this can be a major annoyance for your user. Using Set Variable and Set Field avoids that issue.

              In your script, you have a paste, but no copy, so even your method won't work--I suspect you accidentally left the copy step out of your post.

              You can edit your original post and upload an image there--that's easiest. You can also upload an image to a file sharing site and post a link to the image in any post of this thread.

              To copy/paste scripts into the forum:

              1) You can use FileMaker Advanced to generatea a Database Design Report and copy scripts from the report
              2) You can use Manage | Scripts to print a script to a PDF. Then open the PDF with your PDF reader and use it's text tool to copy the text to your clipboard for pasting.

              • 4. Re: Finding matching records in two tables
                philmodjunk

                I need to clarify  to open a new window and if not open and once the new window is open not to open a second and third.

                You can use the WindowNames function (WindowNames ( Get ( FileName ) ) to get a list of window names. You can use the FilterValues function to compare a window name in quotes to this list. If the name is in the list, the window is open.

                If [IsEmpty ( FilterValues ( WindowNames ( Get ( FileName ) ) ; "YourWindow'sName" ) ) ]
                   New Window [.....
                Else
                   Select Window [.....
                End If

                How can I make this better for a user so instead of looking for a 0 they would see 0= "Special", 1 = Not Available?

                Presumably this is a calculation field returning 0 or 1...
                1) If you can get your labels down to 7 letters or less, you can use the inspector to specify a different label for these boolean values.

                2) Change your calcualtion field to return the text: "Special" or "Not Available" in place of 1 or 0.

                3) Remove the field from your layout and add both labels as simple layout text. Use conditional formatting to set the text size to 500 if the label does not apply. (If field is 1, set Not Available text to size 500. If Field is 0 set Special test to 500). Specifying that large font size makes the text disappear from your layout.

                • 5. Re: Finding matching records in two tables
                  bookalaka_1

                  I tried   If ( test ; resultOne ; resultTwo ) = If ( IsEmpty_Restaurant ;Special;NA) is asking for a field?.  What book do you recommend for script examples?

                  • 6. Re: Finding matching records in two tables
                    philmodjunk

                    I'm not familiar enough with the current crop of books to make a recommendation.

                    If ( IsEmpty_Restaurant ; "Special" ; "Not Available" )

                    PS. IF "NA" is all you want for "Not Available", you might want to just use boolean formatting on the field and specify "Special" and "NA" as the text to be displayed for the values 1 and 0.