12 Replies Latest reply on Jul 10, 2015 2:50 PM by philmodjunk

    Relational database: import conditional records FmPro11

    Karel

      Title

      Relational database: import conditional records FmPro11

      Post

      Relational 1: database: inputmode for new members
      In this database our receptiondesk makes all memberdetails ready.
      Final action: ready for memberschip. This I have out in a valuebox named 'status' with final checkbox: READY.

      Relational database 2: Memberadministration.
      Here the administration ONLY wants the new added members with status 'Ready' (imported or copy)

      So what is the route?
      - Database 2: perform 'find' value markerd ready //copy record ??
        I checked 'import record" but that cannot be made conditional on a value?
      - Which are the richt script-staps to be made?

      Thank you for helping

      Import_records.jpg

        • 1. Re: Relational database: import conditional records FmPro11
          GuyStevens

          Hey Karel, alles goed?

          You can import only a found set if you leave that file open.

          So you go into the first file, find the records you would like to import. Then go to the other file or table and import.

          You will now only be importing the found set.

          But why are you using two different files? I would put all of this in one file providing that that is possible network wise.

          Greetings Guy

          • 2. Re: Relational database: import conditional records FmPro11
            Karel

            Hey Guy  Goedemorgen!

             

            Indeed I have it in one file! As a relational database with two tables. Sorry for using the wrong word, it is one database with to tables!

            Table 'Input' and table 'Membership"

            So this ionternal process of result "became a member >> forward to table Memberadministration"  or: "Memberadministration >> import new members" Either of these routes must be possible.

             

            Thank you!

             

            Karel

            • 3. Re: Relational database: import conditional records FmPro11
              GuyStevens

              Hi Karel. 

              I wouldn't be moving people over from one table to another. But if you need to I think there is an easier method for doing this.

              You can simply set the person's data as variables. Then delete the record in the first table (If it needs to be deleted).

              Then you go to the second table, create a new record and fill it in with the variables.

              This way the person has arrived in the second table.

              Setting a variable means that you "remember" a value in a field.

              So you will have to set a variable for each field. For the name, email, phone, ...

              Then in the second table you simply use the set field script step to set the value of each variable in the correct field.

              Pretty simple stuff.

              If you can't figure it out let us know.

              A script for this would look kind of like:

              Set Variable ( $Voornaam ; Tabel1::Voornaam )
              Set Variable ( $Roepnaam ; Tabel1::Roepnaam )
              ... ( Do this for all of your fields)
              Delete Record ( Perform Without Dialogue)  But only if the record needs to be deleted in the original table.
              Go To Layout (Table 2)
              New Record / Request
              Set Field ( Table2::Voornaam ; $Voornaam)
              ... Do this for all variables
              Show Custom Dialogue ( Client has been imported )

              Something like that.
               

              • 4. Re: Relational database: import conditional records FmPro11
                Karel

                Hello Guy,

                sorry but this is not what I mean. As there are two administrators, one for the entering of new subscriprions (which goes to many details in the portfolio of the aspirant memer) and when this job is finished the second person takes the jobin the member-administration.

                I do not want them to work in the others 'table, so each person follows its own table in the seperated relational database.
                So there much be a bridgefunction through a script of importing new approved incoming members from table 1 to table 2.

                Hope this will clear my needs?

                 

                karel

                 

                • 5. Re: Relational database: import conditional records FmPro11
                  Karel

                  Guy, i been trying to improve a script and it seems to work. see enclosed picture.

                  Step 1 Perform Find (restore)  =  find in Table "entries" the status checked as "LID" (means member)

                  Step 2  import records

                  Step 3 copy record request

                  Whereby only new recordsare allowed if they are new, so no double import of existing records in tabel "Members"

                   

                  I don/t think is is perfect, but the first test is working
                   

                  • 6. Re: Relational database: import conditional records FmPro11
                    GuyStevens

                    Hey Karel,

                    I would indeed do something like that but you can make your script a little bit more detailed. I think you are just barely getting there now.

                    First of all the question is: Who is doing the import? The person on table 1, or the person on table 2, or both?

                    You could indeed perform a find and then import that found set into the second table. But you could also mark the records that have already been imported and then add that to your find so you never import people twice to begin with.

                    It is of course perfectly possible to set the import so it never imports the same record twice. But it's possible to mark already imported records, that way your found set will be smaller, your import will go faster and there is less chance of error.

                    But your way might just do the trick and be fine enough.

                    Just perform a test and if all is good then there might not be a need to change.

                    • 7. Re: Relational database: import conditional records FmPro11
                      GuyStevens

                      P.s. I just noticed your third step, what is that supposed to do?

                      That looks like it doesn't make sense.

                      Also, if I perform a find I always use the script steps:
                      - Enter Find Mode ( Uncheck Pause )
                      - Set Field ( Table1::Status ; "Member" )
                      - Set Error Capture On
                      - Perform Find
                      - If   Get(FoundCount) > 0
                      -    Import Records
                      - Else
                      -    Beep
                      -    Show Custom Dialogue ( There are no new members )
                      - End If
                      - Show All Records
                      - Sort records

                      This way you can use as many "Set Field" steps if you like. So you can look for "Member" but also search for a value in a field that mark people that were already imported.

                      • 8. Re: Relational database: import conditional records FmPro11
                        Karel

                        Hello guy,

                         

                        unfortunately: it does not work.
                        First bug: screen "open file" request intervenes. Even if I  put this extrastep in the script it remains as pop-up screen.
                        Then secundly pop up screen "inport field mapping' intervenes
                        Then thirdly the "sort' menu pop ups.
                        But indeed: after these inasked interuptions, it works!

                        See enclosed scriptprint

                         

                         

                         

                        • 9. Re: Relational database: import conditional records FmPro11
                          GuyStevens

                          Hey Karel,

                          Certain script steps have the option "Perform Without Dialogue" that you can check.

                          If you check that you will not get a pop up screen.

                          The Import script has one and so does the Sort script step.

                          The important thing is however that you correctly set your settings for the import and the sort scripts in your scrips.

                          You have to determine beforehand fron which file you want to import, which table you want to import into which table and how the field mapping is done.

                          You need to set all these things beforehand in your script and then that script can be performed without dialogue and the user will never see anything happen.

                          The sort script step is the same, it can be performed without a dialogue but then you do have to correctly enter the settings beforehand.

                          Greetings

                          Guy

                          • 10. Re: Relational database: import conditional records FmPro11
                            Karel

                            Guy, thank you for your patience, but a small detail must be missing.

                            I tried several options in the script, but still I think some line is missing. The function: import only when the Status Field is marked as "Lid" is not properly defined. Maybe you see some missing detail?

                             

                            • 11. Re: Relational database: import conditional records FmPro11
                              GuyStevens

                              I don't understand why you have specified criteria in your perform step.

                              If you go into find mode, set the field "status" to contain the value "Lid" then you only need to add a "Perform Find" script step.

                              There is no need to add criteria in there.

                              If you have Filemaker Pro Advanced you can use the script debugger to step through your script step by step as it is performing to see where things go wrong If you don't have Filemaker Pro Advanced you can add "Pause" steps in your script to temporarily pause the script so you can see if everything is going well. You could place a "Pause" right after the perform find to see if that worked or not.

                              The fact that you are only importing contacts with the status "Lid" is done by this find. So if this Find isn't being performed properly then your import won't be correct.

                              I also suggested to mark the records of the members that were already imported so you don't import them twice. I would suggest you do that so you don't import the same people twice.

                              Right after you performed your find in the first table you can add a "Replace Field Contents" script step that enters a value in a field. 

                              This does mean that your original find needs to check if that field is empty. Finding an empty field can be done by setting the field to = while in Find mode. 

                              But maybe this is something you should add after the rest works.

                              • 12. Re: Relational database: import conditional records FmPro11
                                philmodjunk

                                If you don't have Filemaker Pro Advanced you can add "Pause" steps in your script to temporarily pause the script so you can see if everything is going well.

                                An even better way to pause the script at key points when debugging is to use Show Custom Dialog. With this step, you can not only pause the script, but use the dialog to display the value of key variables or fields that might not be visible on the current layout.