1 2 Previous Next 19 Replies Latest reply on Feb 9, 2016 8:18 AM by disabled_morkus

    GetNextSerialValue() Not Returning Anything

      I'm doing an Excel import in a child table each week that's tied to a parent record for that week. Obviously, in the child (excel imported data) table, there will be multiple records for the given week's parent PK.

       

      The problem I have, since there is no ultra-simple and quick one-liner "UPDATE" SQL (Grrrr) is that I need to update the child table's FKs in a loop after the Excel import batch process is done. That is, for each excel record imported, I need to update the FK field in the child table so it points back to the particular parent record.

       

      Since the excel import happens in a batch and there is no simple UPDATE SQL I'm left with, as I understand what's possible, having to know the first record PK value imported and, after the import, going to the bottom, and getting (in a variable) the last PK value imported.

       

      Then I can set up a loop to update each FK field in the child table so it correctly points back to the parent.

       

      Now, since the PK field in the child table is auto-incrementing, from import to import, I have no idea what the PK value currently is without some help.

       

      it looks like the GetNextSerialValue is what I want, but it doesn't return anything. If I go into the database, I can easily see that for the child table's PK there is a "next value", but the command below returns nothing.

       

      GetNextSerialValue (Get(FileName) ; CHILD_TABLE::PK_FIELD )

       

      Would therefore appreciate any suggestions why this command does nothing.

       

      Thanks,

       

      - m

       

      P.S. Maybe there's a more elegant approach to my import approach?

        • 1. Re: GetNextSerialValue() Not Returning Anything
          DavidJondreau

          You've got me pretty confused..I'm not sure where the foreign key is coming from. Or are you trying to replace the PK of the child? Your post isn't clear. Do all the records in each batch have the same new FK? Where does the FK (parent  PK) come from? Can you post the scripts or a sample file?

           

          I don't know the Serial Increment function or script steps are what you want. But there are other options...There are Import[] options to auto-increment. Have you looked into the Replace[] script step? If you know the FK before the import, you can set up an auto-enter calc that references a global variable.

          • 2. Re: GetNextSerialValue() Not Returning Anything

            Hey David,

             

            Thanks for your reply.

             

            The basic issue is that I have a field that has a serial auto-increment turned on, but

            GetNextSerialValue (Get(FileName) ; CHILD_TABLE::PK_FIELD )


            returns nothing on that auto-increment field.


            I was going to use that value to help me figure out the range of records that the Excel batch just imported so I can update all the FK fields in the child table with the single PK value for the parent.

             

            Not sure how to use the GetNextSerialValue(), apparently.

             

            Does this function work for you in FMPA 14?

             

            Thanks in advance,

             

            - m

            • 3. Re: GetNextSerialValue() Not Returning Anything
              DavidJondreau

              Well, I don't think the technique you're using to accomplish the result you want is a particularly good way of going about it. But you seem pretty set on it.

               

              I recommend wrapping the CHILD_TABLE::PK_FIELD parameter in a GetFieldName(). So, GetNextSerialValue (Get(FileName) ; getFieldName ( CHILD_TABLE::PK_FIELD ) )

              • 4. Re: GetNextSerialValue() Not Returning Anything

                Not sure which technique you're referring to. Since SQL UPDATE doesn't exist in FMP, you pretty much have to do a loop, right? And since an excel import is all done in a batch with no programmatic control, then you need to figure out which and how many records got imported. The part of linking the child records back to the parent is basic DB stuff so I'm sure you're not referring to that.

                 

                I could also create a temp table, import to there and then take my time copying over data to the real child table.

                 

                In any case your getFieldName() idea worked fine. Yet, the example I found for the GetNextSerialValue() on the FileMaker Web site doesn't seem to "need" it. Confusing.

                 

                In any case, thanks!

                 

                - m

                • 5. Re: GetNextSerialValue() Not Returning Anything
                  DavidJondreau

                  Glad you got it working.

                   

                  There are lots of ways to count records and to update records. I suggested a few alternatives in the second paragraph of my first post.


                  If you just want to know the number of records imported, you can start on the target layout with an empty found set before the import and after the import the function Get ( FoundCount ) will tell you how many were imported.

                  • 6. Re: GetNextSerialValue() Not Returning Anything
                    nicolai

                    morkus


                    You obviously did not read the documentation on the function properly. Personally, I found it fairly straight forward, but hey, I am a FileMaker developer and we are simple-minded, right? Real men do not read manuals.

                     

                    I say it again, if you are missing SQL so much, get a plugin and SQL you way through you databases as much as you want. You will need to read documentation on this, unfortunately.

                     

                    What really gets me, is that David Jondreau spent his valuable time trying to help with your confusing posts and being super patient and you do not mark his answer as correct and sound fairly rude in the end. Smily face does not make it better.

                     

                    David, many thanks for dealing with this.

                     

                    Nicolai

                    • 7. Re: GetNextSerialValue() Not Returning Anything
                      DavidJondreau

                      I always appreciate compliments about my advice! Thank you, nicolai.

                       

                      I do think the documentation could use clarification. The field name is in quotes in the doc and I'm sure it was last edited pre-GetFieldName.

                       

                      And I'll take smiley faces all day long!

                      • 8. Re: GetNextSerialValue() Not Returning Anything
                        nicolai

                        David Jondreau wrote:

                        And I'll take smiley faces all day long!

                        There you go:

                         

                        I do think the documentation could use clarification

                         

                        OK, I've read it again and, as you pointed out, it is confusing. My sincere apologies to morkus regarding not reading documentation, everything else stands.

                        • 9. Re: GetNextSerialValue() Not Returning Anything
                          user19752

                          Hmm?

                           

                          There need quotation, nothing is confusing. In example

                          GetNextSerialValue(“Customers”;”CustID”) returns the next serial number for the CustID field.

                           

                          so in his implementation it should be

                          GetNextSerialValue (Get(FileName) ; "CHILD_TABLE::PK_FIELD" )

                           

                          Design functions need field name as text, not field reference.

                           

                          Using GetFieldName() function is robust for renaming.

                           

                          In addition, Get(FileName) can be "", I learned here.

                          1 of 1 people found this helpful
                          • 10. Re: GetNextSerialValue() Not Returning Anything
                            beverly

                            nicolai said in whole or in part:

                             

                            I do think the documentation could use clarification

                             

                            That's the key here. An idea I'd always had was a way to post Errata on the documentation. There always seems to be some! I suppose that's one reason this forum is a must, so we can help each other discuss what the documentation really says (or should say).

                             

                            beverly

                            • 11. Re: GetNextSerialValue() Not Returning Anything

                              Dude, please chill. This isn't the first time you have left a heated unhappy reply that doesn't even concern you.

                               

                              There wasn't any intended rudeness. Seriously!

                               

                              I totally appreciated David's reply and said so. The fact I didn't explicit mark his answer as "CORRECT" probably means I was busy working on my project not ignoring David's contribution. Why is it up to you to get so angry?

                               

                              The actual problem was that I forgot to include the second parameter in quotes as pointed out by 19752.

                               

                              Simple mistake. (No FMP Nanny Police needed.)

                               

                              Let's not escalate innocuous postings into something they're not, OK?

                               

                              Thanks,

                               

                              -- m

                              • 12. Re: GetNextSerialValue() Not Returning Anything

                                Thanks very much.

                                 

                                That's what I missed.

                                 

                                Simple mistake....appreciate your reply.

                                 

                                - m

                                • 13. Re: GetNextSerialValue() Not Returning Anything
                                  beverly

                                  Would GetFieldName

                                  help here?

                                   

                                  GetNextSerialValue( Get( FileName ) ; GetFieldName( CHILD_TABLE::PK_FIELD ) )

                                  • 14. Re: GetNextSerialValue() Not Returning Anything

                                    Hi David,

                                     

                                    I just wanted to thank you again for your replies yesterday.

                                     

                                    As pointed out, I had forgotten to quote the field name in the second parameter so your idea of using the getFieldName() function worked, but was confusing as to why it was needed.

                                     

                                    Now I see that I hadn't read the documentation explicitly. I was really thinking that with the second parameter being in quotes meant it as an "example", not that you really needed to quote the second parameter.

                                     

                                    Before posting, I had done a couple of searches and found that there was confusion on this function.

                                     

                                    Again, thank you very much. Your initial reply helped me get past my initial issue and I got that feature completed.

                                     

                                    -- m

                                     

                                    To update the FKs, I ended up doing a FIND and then a REPLACE FIELD CONTENTS with the FK value. That worked fine and was a simple workaround (and FMP equivalent I suppose) to a SQL UPDATE.

                                    1 2 Previous Next