11 Replies Latest reply on Jan 9, 2013 10:33 AM by philmodjunk

    Only Import Records based on find

    NetDude

      Title

      Only Import Records based on find

      Post

           Scenerio: Only Import records based on the absence of a Variable ID via the Enter Find Mode function

           Set Variable[$VendorID; Value:Vendor::ven_id ---- STEP ONE IS TO SET VARIABLE BASED ON THE CURRENT RECORD
      Show Custom Dialog ["Alert"; "Here is the ID of the variable"; & $VendorID] ---- USED FOR ENSURING THAT THE VARIABLE IS CORRECT
           Go to Layout ["x_vendor_component_function"(x_vendor_component_function)]
           Enter Find Mode --- STEP TWO GO TO LAYOUT AND ENTER FIND MODE
           Set Field [x_vendor_component_function::ven_id = $VendorID] 
           Perform Find [] ---STEP 3 SET THE FIELD ven_id TO THE VARIABLE ESTABLISHED IN STEP 1

           // NEXT IT TO SET ANOTHER VARIABLE ACCORDING TO HOW MANY RECORDS WERE FOUND
           Set Variable [$VenIDCount; Value:Get (FounfCount)
           // IF THE RESULTS GENERATED INTICATED 1 OR MORE RESULTS THEN SHOW ALERT
           If [$VenIDCount ≥ 1 
            Show Custom Dialog ["Alert"; "There is a record that already exists in this table"]
           // ELSE MOVE FORWARD WITH IMPORTING
           Else
            Import Records ["Functional Requirements.fmp12"; Add Mac Roman]
            Replase Field Contents [No Dialog; x_vendor_component_fuction::ven_id; $VendorID]
           End If

            

           So the first time a record is created the expected results would be that FM throws up a "No Records match this find crieria" This works upon executing the script from a button located on the main record page.
           Q1: Is there a way to overide it so that a user does not see this uaotmated prompt but rather the scripts simply just continues running

           After hitting continue the import process begins and the user can view the various fields setup in the import field mapper
           Q2) Is there a way for this pop-up "Import Field Mapping" not to display but rather to move on with the import process

           When cmpleted the Import Summary window is now displayed, great way to assure the correct number of records are being imported Cool

           Now lets say the user accidentaly hits the button to run the import script, emmediately the user receives the same message "No records match this find criteria" 
           Q3) Why, The record does infact exist where by resulting in an expected record count of somesort. In this case the above script should simply alert then and nothing more.

        • 1. Re: Only Import Records based on find
          philmodjunk

               You have a mistake in your set field script step:

               Set Field [x_vendor_component_function::ven_id = $VendorID]

               should be:

               Set Field [x_vendor_component_function::ven_id ; $VendorID]

               When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

          • 2. Re: Only Import Records based on find
            NetDude

                 Ok, I see your point. Prior to your help I wrote the script using a Perform Quick Find instead. I used dialogs and stepped through tthe script as I was building

                  

                 When the script gets ran the very first time it works. When I try and re-run it against the same record the script indicates that it could only find 25 records out of the 95 that I know exist. Then it does what it is suppose and only goes as far as the first IF statement. I'm very perplexe that it only indicates 25 Records found when in reality there shuod be 95 that match the criteria for the VendorID

                 When I add another Vendor Record and then execute the scripts via a button on the vendor layout page. I receive positive feed back that my variable $VendorID is for the new record, I then receive a "No Records match this find" dialogue which is exactly what I would expect HOWEVER then I receive my dialogue box indicating 95 records exist in the x_vendor_component_function table for this vendor. Which is not true according to the previoud alert indicating no records could be found. Directly following I then receive my dialogue box (within the first IF statement) indicating that the record has already been auto populated.

                 I’m not sure what the hell is happening but I have been working on this for 4 hours and still noting is correct. I’m really beginning to dislike scripting. 

                  

                 Long live aggregated correlated sub-queries, MS SQL Server Rocks, youd think I could figure this stuff out. LOL Any Help would help!!!

                  

                 Thanks again!

            • 3. Re: Only Import Records based on find
              philmodjunk

                   Your perform quick find has the same issue as the set field step. You are entering data here, not an expression.

                   only the variable name should appear between the square brackets.

              • 4. Re: Only Import Records based on find
                NetDude

                     The only difference being is that there is only one specify button for a Quick Find.

                     Let me ask you: Would it be easier to incomporate conditional logic for displaying  or not displaying the button/text based on wheather or not the vendor record exists in another table?

                • 5. Re: Only Import Records based on find
                  philmodjunk

                       Quick find searches all fields enabled for quick find where set field modifies the contents of a specified field--hence two specify buttons for set field and only one for quick find.

                       If you have a relationship matching by vender, a conditional format expression can use that relationship to make text appear or disappear. If you set the font size of a field, button or layout text large enough (100+ is usually large enough), the text will be invisible.

                       Without the proper relationship, this method will not work.

                  • 6. Re: Only Import Records based on find
                    NetDude

                         Here is a great example (see below) of the behaivor I am getting: The first record added, properly steps through the script as intended in order to populate the Components/Function Portal table. However adding the 2nd record indicates the correct Vendor ID, but indicates that no records were found however the data behind the message box indicates there are records, then the second window indicates there are 95 records, 3rd window indicates the record has already been auto populated but it hasnt. Script then finished and never goes through the import process

                         Here is a link to the video

                    http://www.inhousecabinets.biz/other/FileMaker_Example.html

                          

                         Screen Shot of Script used

                          

                    • 7. Re: Only Import Records based on find
                      philmodjunk

                           Why are you using quick find for this? That can find records where the value in $VendorID was in a different field than what you want here.

                           You'll need to describe the design of your layout and if there is a portal here, describe that relationship.

                           Please note that neither quick finds nor standard finds search records in a portal's table, they query the layout's table.

                      • 8. Re: Only Import Records based on find
                        NetDude

                             Thanks again Phil. Let me first explain what I would like to do:

                             Only display a button/text based on the presence of the ven_id for the current record existing in another table called x_vendor_component_function

                             Vendor Layout
                             Allows users to add new vendor record information. i.e. vendor name, address, etc...
                             There is also an area on the layou that uses a multiple Portal displays within tab control.
                             When a new vendor record has been created the various portal (I.e HIE, Provider Portal, Patient Portal, Analytics, Reporting, Care Management, CDR) tabs are not yet populated.
                             The only way they get populated is for a user to press a button or text that will run a script for importing data from a different table x_function_component. When this data is populated the current vendor id is replaced for every record entry into x_vendor_component_function::ven_id. Each portal has been setup within each tab with s specific folter for identfying only the data needed for that portal based on sc_id.

                             I have attached a screen shot that includes the Vendor Layout, DB diagram and script that is used for importing records. On the Vendor layout page you will see txt (middle right of template) that says "Auto Populate" this where I need to determine if the current vendor id is present in x_vendor_component_function::ven_id if it is THEN DO NOT display the button/txt for running the script. This ultmately prevent double entry , which is all Im trying to avoid.

                        • 9. Re: Only Import Records based on find
                          philmodjunk

                               There is no need to perform any kind of find here.

                               You already have this relationship:

                               Vendoer::VEN_ID = x_vendor_component_function::ven_id

                               From your vendor layout

                               IsEmpty ( x_vender_component_function::ven_id )

                               will return True if there are no related records in x_vendor_component_function for the current vendor record.

                               You can use this in a conditional format to make text appear or disappear--such as text for a button and you then put:

                               If [ IsEmpty ( x_vendor_component_function::ven_id ) // no related records exist in x_vendor_component_function ]

                               Put your script steps to import records here

                               End IF

                               in your script to control whether or not this imports records.

                          • 10. Re: Only Import Records based on find
                            NetDude

                                 Cool thanks again!!

                                 FYI:: In specifying the calculation for the conditional logic for my text "Auto Populate" IsEmpty (x_vendor_component_function::ven_id)

                                 The tag at the bottom of the page indicates that Non-Zero value are true, zero and empty values are false

                                 So in the case there are no records found IsEmpty (x_vendor_component_function::ven_id) will return False is there are no related records in x_vendor_component_function for the current vendor record. This is the opposite of what you said

                            • 11. Re: Only Import Records based on find
                              philmodjunk

                                   It's not the opposite. Note that I said Appear OR Disappear.

                                   You can format the text to be invisible and use an expression to make it visible or you can format text to be visible and use an expression to make it invisible.

                                   And you can also use

                                   Not Isempty(   ) to reverse the logic of your boolean expression.

                                   if ven_id is a field of type number, you can even remove the IsEmpty function altogether.

                                   In which case, Table::ven_ID is the equivalent of Not IsEmpty ( table::ven_ID ) and Not Table::Ven_ID is the equivalent of IsEmpty ( Table::Ven_ID )