1 2 Previous Next 19 Replies Latest reply on Oct 5, 2015 12:05 PM by maestrodevelopment

    How to split a table in FMP ?

    Boblebad

      Hi

       

      I have a table that holds all the information about the content of a magazine. The table comes from a website. There's probably a reason for it.

       

      Is there a special way to split the table easily in FMP 14 Adv. ?

       

      I need to get the issue number and year out, so i can make a main Layout where i can put a Portal that can show the content of the single issues. Around 10-15 articles in each magazine.

       

      I know how to build this from scratch. But as there's around 900 magazines, it would be nice to be able to do it with the table at hand.

       

      All the best

      Carsten

        • 1. Re: How to split a table in FMP ?
          Boblebad

          There's probably one important info to this.

           

          The numbers of the issues are reset every year, so they start over from issue 1 again.

           

          I'm thinking that manually adding the FK to the main table is the easiest way to do it, but i need a way to do it via script be course there's around 11.000 records, and that will take a loooong time entering every digit by hand ;-)

           

          Is there a way to make a script keep an eye on two fields and then updating a third ?

           

          Ex. there's 11 articles in issue 1 from 1933 and then there's 13 from issue 2, 1933 aso. Is it possible to make a loop looking for first the year and then checking the issue number and then updating the FK field based on what it was before that, so that every issue 1 article from 1933 gets FK 1, and issue 2, 1933, FK 2 aso.

           

          EDIT: There might be a little hick up more to it. There is not an equal number of issues each year.

          • 2. Re: How to split a table in FMP ?
            erolst

            OK, so you have a table, let's call it “Article”; to normalize your data, you need another table "Issue", and proceed like so:

             

            • make sure Article has a foreign key field, say “id_issue”

            • create a new table Issue, with fields for primary key, year, issue, and a number field isDuplicate (see below)

            • go to an Issue layout and import your existing Article records (import only the year and issue fields into their Issue counterparts)

            • write a script to flag duplicates in Issue and delete them (*see below)

            • relate Issue and Article by

            Issue::year = Article::year

            Issue:issue = Article::issue

            • go to Article and Show all records

            • use Replace Field Contents with

            target field:: Article::id_issue ; calculated result: Issue::id

            • redefine the relationship as
            Issue::id = Article::id_issue

            • place a portal into Article on an Issue layout

            • step10: there is no step 10! ;-)


            *De-dup like so:


            Go to Layout [ Issue [ Issue ) ]

            Show All Records

            Sort Records [ by year, issue ]

            Go to Record [ first ]

            Loop

              Set Variable [ $thisIssue ; Issue::year & "|" & Issue::issue ]

              If [ $thisIssue = $previousIssue ]

                Set Field [ Issue::isDuplicate ; 1 ]

              End If

              Go to Record [ next ; exit after last ]

              Set Variable [ $previousIssue ; $thisIssue ]

            End Loop

            Enter Find Mode

            Set Field [ Issue::isDuplicate ; 1 ]

            Perform Find

            Delete All Records [ no dialog ]


            That's the basic idea for normalizing an existing set without existing parent records.


            If you do later data imports that you want to normalize, you may have to watch out for existing Issue records that fit the new Articles; so you may wan to keep a second relationship around that matches on year and issue, and first check on matching Issues.


            Note also that you can do a faster de-duplication by exporting Article records grouped by a year|issue calc field, then importing that result into Issues – but that's more difficult to describe.

            • 3. Re: How to split a table in FMP ?
              Boblebad

              Sorry for my late reply, my work has kept me busy, and thank you erolst

               

              I'll have a look at it later today.

              • 4. Re: How to split a table in FMP ?
                Boblebad

                Nothing happened. It ended on the search page with no records at all. But all records are still in the table, none deleted.

                 

                I had only one problem. I could not use " ; " in this line Set Field [ Issue::isDuplicate ; 1 ], so i used " = " instead.


                 

                • 5. Re: How to split a table in FMP ?
                  erolst

                  Boblebad wrote:

                  I had only one problem. I could not use " ; " in this line Set Field [ Issue::isDuplicate ; 1 ], so i used " = " instead.

                   

                  You're not supposed to use it; in script code, the semicolon is merely a device to separate the input for the different step options – which for Set Field consists of the target field and the calculated result. The first one you must select from a dialog, the second one enter into the Specify/Edit Calculation box.

                   

                  Since you thought all this had to go into the calculation – what did you select as target field?

                  • 6. Re: How to split a table in FMP ?
                    Boblebad

                    I'm not sure what is what here.

                     

                    What is the calculated result, i get the editor and has to chose something, and i don't know what ?

                     

                    EDIT: When looking at the table "isDuplicate", it seams that the script works sorting out the duplicates. The first incident is empty, the rest of them is 0'ed. So it looks like it's the deletion of the 0'ed records that's not working as it should.

                    • 7. Re: How to split a table in FMP ?
                      thurmes

                      In Erolst's script, the target field is Issue::IsDuplicate. You set that to 1 (because it met the condition $ThisIssue = $PreviousIssue); 1 means True. Then, later on, you find all records in which you set IsDuplicate to 1, and delete them.

                       

                      If you want to go a bit more slowly and assure yourself that those records in which IsDuplicate is 1 really are duplicates before you delete them, you can omit the last four steps from the script and do them manually; put them in later once you're confident things are working right.

                      • 8. Re: How to split a table in FMP ?
                        Boblebad

                        Thank you thurmes

                         

                        I just added this edit to my reply:

                        "When looking at the table "isDuplicate", it seams that the script works sorting out the duplicates. The first incident is empty, the rest of them is 0'ed. So it looks like it's the deletion of the 0'ed records that's not working as it should."

                         

                        EDIT: And what you wrote that i should do, was what i did.

                        • 9. Re: How to split a table in FMP ?
                          erolst

                          Boblebad wrote:

                          I just added this edit to my reply:

                          "When looking at the table "isDuplicate", it seams that the script works sorting out the duplicates. The first incident is empty, the rest of them is 0'ed. So it looks like it's the deletion of the 0'ed records that's not working as it should."

                           

                          The field contains a 0 in all but the first record because that is the result of the expression that you put in (which is incorrect for that purpose).

                           

                          If you configure the Set Field[] step as it should, the correct records will be flagged as duplicates.

                          • 10. Re: How to split a table in FMP ?
                            Boblebad

                            That i don't understand, cause i sat it to "1". How can it end up as a "0" ?

                             

                            Isn't there a way to copy the Script Workspace ?

                             

                            Screen Shot 2015-10-04 at 11.29.47.png

                            EDIT: I couldn't figure out how to get the sorting in place. But the records are sorted from the start, so that's not a problem.

                            • 11. Re: How to split a table in FMP ?
                              erolst

                              Boblebad wrote:

                              That i don't understand, cause i sat it to "1". How can it end up as a "0" ?

                              No, you didn't set it to 1.

                               

                              Set Field [ Kosmos_Issue::isDuplicate ; Kosmos_Issue::isDuplicate = 1 ]

                               

                              sets the field Kosmos_Issue::isDuplicate to the result of the expression Kosmos_Issue::isDuplicate = 1; that result is the Boolean value False (because that field is (supposed to be) empty, so it is ≠ 1), and that is the numeric value 0.


                              The 'Kosmos_Issue::isDuplicate =' part is already implied in your selecting that field as the target field.


                              May I kindly suggest that you study the basics of writing calculations and script steps in FileMaker?


                              Boblebad wrote:

                              Isn't there a way to copy the Script Workspace ?

                              If you happen to use OS X, you can

                              • open the script

                              • select File - Print

                              • in the Print dialog, select “Open in Preview” from the PDF popup menu

                              • copy the text from the PDF window

                              • paste it

                              • read over it and correct any wrong line breaks

                              • 12. Re: How to split a table in FMP ?
                                Boblebad

                                Wau, that was some trick to copy from the Script Workplace ;-)

                                 

                                Yes, i'm on OS X. But can't i really not copy to and from the Script Workplace ?

                                 

                                Well, i'm new to both Mac and Filemaker Pro 14 Adv. I'm trying to figure this out. Somethings are a lot easier than in Access, but somethings are also the direct opposite.

                                 

                                And I'm not sure if we use the same version of that Script Workplace, be course i can't just do what you want me to do. To me your reply looks like that i should only have one incident "Kosmos_Issue::isDuplicate" in the Set Field expression. And then there's the "1" you use, what is that for if it's not the actual value ?


                                EDIT: And i don't understand why a "1" suddenly becomes False. What i have learned is that the True statement to the question is always "1", and then "0" is False ?

                                • 13. Re: How to split a table in FMP ?
                                  erolst

                                  Boblebad wrote:

                                  To me your reply looks like that i should only have one incident "Kosmos_Issue::isDuplicate" in the Set Field expression. And then there's the "1" you use, what is that for if it's not the actual value ?

                                  Would you like me to type more slowly?

                                   

                                  For your specific Set Field[] step:

                                  select the target field: Kosmos_Issue::isDuplicate

                                  specify the calculated value: 1

                                   

                                  which will result in a script line that looks (more or less) like:

                                  Set Field [ Kosmos_Issue::isDuplicate ; 1 ]


                                  which happens to be the same format as in my original code suggestion.


                                  Translated into plain English this reads :

                                  Set (the) field Kosmos_Issue::isDuplicate (to the result of the calculation) 1

                                   

                                  Boblebad wrote:

                                  EDIT: And i don't understand why a "1" suddenly becomes False. What i have learned is that the True statement to the question is always "1", and then "0" is False ?

                                   

                                  You didn't enter a 1; you entered 'someTable::field = 1', which is a predicate that is either True (if that field's value is 1) or False – and what is entered is the numeric equivalent of that result since, i.e. 0.

                                   

                                  Boblebad wrote:

                                  Yes, i'm on OS X. But can't i really not copy to and from the Script Workplace ?

                                   

                                  Copying script code directly as text: not without a plug-in.

                                  Pasting to convert text into script code: no

                                   

                                  btw, it is Script Workspace.

                                  • 14. Re: How to split a table in FMP ?
                                    Boblebad

                                    Thank you erolst

                                     

                                    Well, you wrote it to fast, i got left by the side on the first corner ;-)

                                     

                                    And well again, this only changed the 0's to 1's. The deletion does not work. No record is deleted.

                                     

                                    Regarding the calculation, i actual read the pages here on the site and also in The missing manual, but i didn't figure out the part that you just can enter a value without an operator.

                                    1 2 Previous Next