1 2 3 Previous Next 31 Replies Latest reply on Jan 6, 2017 7:44 AM by PeterDoern

    Why Can't I Create a "Dummy Record" (to be used as a "Custom Header" for Excel Report)

    sansae

      Hello all,

       

      I hear that a simple way to create custom headers for an excel report is to create a dummy record in your layout. However, I'm having trouble creating that dummy record.

       

      After I create the new record in my layout, I'm unable to set my fields. When I step through "Set Field [table::field; "text"]", I get a "record is missing" error even though my new record has just been created.

       

      An interesting note is that when I skip the "New Record/Request" script step and instead step through "Set Field ...", I'm able to set every field in one of my pre-existing records.

       

      Why can't I create a dummy record?

       

      (As I understand it, after we create that record, we should sort the records in such a way that the dummy record is the first on the list. From there, we should uncheck the "use field names as column names in first row" (found under "save records as excel" options dialog). The idea being that the first dummy row will serve as the custom column header.)

       

      Message was edited by: San Sae Changed title of post and question in body

        • 1. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
          BruceRobertson

          This assumes that all the fields you will ever select for export will always be editable text fields.

          Doesn't seem very likely.

          • 2. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
            jonack70

            I am always hoping that FM comes out with better integration with MS Office,,

             

            there are plug-ins-- but that costs time & $$

             

            the only answer for now is to use Stylesheets-- not so easy

            • 3. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
              Malcolm

              Rather than create dummy records in FMP, have you considered doing it on the fly? Search here for xslt exporting by Jens Teich or go to his website DJT Organisationsberatung | Willkommen beim Datenbank Profi  and download the CSV export with XSLT.

               

              Excel will open the CSV file and you have your headers.

              2 of 2 people found this helpful
              • 5. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                sansae

                BruceRobertson wrote:

                 

                This assumes that all the fields you will ever select for export will always be editable text fields.

                Doesn't seem very likely.

                Hi Bruce,

                the fields are all Edit Boxes that have "Browse Mode" unchecked under Field entry. When in browse mode, the user can not click on a field and make changes to it.

                 

                However, when I ran my script, it was able to modify each field. The script step "Set Field ..." ran without an error for all the fields that I "set field" to. So I'm not sure why that happened.

                 

                I'm sure I'm missing something.

                • 6. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                  sansae

                  Malcolm Fitzgerald wrote:

                   

                  Rather than create dummy records in FMP, have you considered doing it on the fly? Search here for xslt exporting by Jens Teich or go to his website DJT Organisationsberatung | Willkommen beim Datenbank Profi and download the CSV export with XSLT.

                   

                  Excel will open the CSV file and you have your headers.

                  I don't think I've come across this method of creating the headers "on the fly".

                  Thank you for the link. I'll take a look.

                  • 7. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                    sansae

                    Thank you for the link, Beverly. Unfortunately, there were some rather inappropriate ads on the site so I left. (I'm at work and I don't feel comfortable reading about fm stuff with a lady showing me her b##bs).

                     

                    Appreciate the response though

                    • 8. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                      BruceRobertson

                      the fields are all Edit Boxes that have "Browse Mode" unchecked under Field entry. When in browse mode, the user can not click on a field and make changes to it.

                       

                      However, when I ran my script, it was able to modify each field. The script step "Set Field ..." ran without an error for all the fields that I "set field" to. So I'm not sure why that happened.

                       

                      I'm sure I'm missing something.

                      I don't understand your point here.

                      The user can't edit the field. OK.

                      The script can set the field.

                      Everything is working the way you asked for.

                      Why do you think there is a problem?

                       

                      But again - you will NEVER want to export an ID field? A number field? Or a calc field? Or a date field?

                      Just doesn't seem realistic.

                      • 9. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                        philmodjunk

                        to expand on Bruce's last post, Layout restrictions on what field can or cannot be edited will not have any effect on whether set field can or cannot modify the contents of the field. Set Field modifies data without interacting with the objects on your layout. That's generally a good thing.

                         

                        I suspect that you may have some sort of data validation that is keeping this from working for you, but that's just a guess. Modify your layout (or make a copy of it and modify the copy) to allow browse mode editing. Then try to manually do what your script can't do: a) Create a new record and then b) start entering data into the fields. This has a good chance of revealing what is keeping this from working for you.

                         

                        With regards to Bruce's other comments, he makes a very good point that this method only works with certain types of fields. The "old school" solution is a cumbersome approach that uses a temporary "staging" table where all the fields are of type text and you move the data into the staging table before exporting. The drawbacks of this approach are pretty obvious and hence the suggestions that you are getting to use a different method to get the data exported with the appropriate column headers.

                        1 of 1 people found this helpful
                        • 10. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                          beverly

                          I so hate all the junk on an otherwise decent web report. My  mobile browser showed none of that. Apologies.

                           

                          Try searching yourself for 'excel and JSON'. Perhaps there are better pages.

                           

                          Sent from miPhone

                          • 11. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                            sansae

                            Bruce,

                            In my original post, I said:


                            "After I create the new record in my layout, I'm unable to set my fields. When I step through "Set Field [table::field; "text"]", I get a "record is missing" error even though my new record has just been created.

                             

                            An interesting note is that when I skip the "New Record/Request" script step and instead step through "Set Field ...", I'm able to set every field in one of my pre-existing records."

                             

                            If I create a new record and attempt a "Set Field..." script step on that record, I get an error telling me that the record is missing. In other words, I end up not being able to create that dummy record because I can't enter any data into any field (for the new record that has just been created) that I attempt a "Set Field..." to.

                             

                            If I DON'T create a new record, I can "Set Field ..." to any field in a record (an original, pre-existing record) without issue. The problem here is that I don't want to change any pre-existing records. I'm not supposed to do that.

                            (I only share this part to point out that I thought this was weird. I can set field to an original record, but I can't do that to a new record?)

                             

                            What I want is to create a new record and add text to each field in that record. That's it.

                             

                            If what I said above still doesn't make sense to you, we can move on because I don't know how else to explain it.

                            • 12. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                              sansae

                              beverly wrote:

                               

                              Try searching yourself for 'excel and JSON'. Perhaps there are better pages.

                               

                              Sent from miPhone

                               

                              If all else fails, will do, Beverly.

                               

                              Thank you much.

                              • 13. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                                sansae

                                philmodjunk wrote:

                                 

                                to expand on Bruce's last post, Layout restrictions on what field can or cannot be edited will not have any effect on whether set field can or cannot modify the contents of the field. Set Field modifies data without interacting with the objects on your layout. That's generally a good thing.

                                This is super helpful. Thank you, Phil.

                                 

                                philmodjunk wrote:

                                 

                                I suspect that you may have some sort of data validation that is keeping this from working for you, but that's just a guess. Modify your layout (or make a copy of it and modify the copy) to allow browse mode editing. Then try to manually do what your script can't do: a) Create a new record and then b) start entering data into the fields. This has a good chance of revealing what is keeping this from working for you.

                                Also super helpful. Thank you again. I'll do that right now.

                                • 14. Re: Creating "Dummy Record" as "Custom Header" for Excel Report
                                  sansae

                                  philmodjunk wrote:

                                   

                                  With regards to Bruce's other comments, he makes a very good point that this method only works with certain types of fields.

                                  That's a very good point. What types of fields?

                                  1 2 3 Previous Next