1 2 Previous Next 23 Replies Latest reply on Nov 26, 2013 1:09 AM by JoeNuc

    XLS export in IWP

    MilutinPetrovic

      Title

      XLS export in IWP

      Post

           Hello folks,

           I know this is a ethernel issue of filemaker but bare with me.

           I have devised a cunning plan to export data to XLS via IWP and its separated in few stages:

           1. When You click an export button Your request is loged in a "Print" table, and the current found set of ID-s of records being exported are copied to tabel "Print order".

           2. On timer script is probing every 60 sec to "Print" table, and when it sees request, it should start the export. 

            

           My prblem is this, fow to perform find to filter the records in Main table to fit the ID-s in Print Order table?

        • 1. Re: XLS export in IWP
          philmodjunk

               We'd need to know more details about your database design. Presumably, you'd need to store a list of ID's as part of the new record in the Print table so that a script could use it with either a find or Go To Related Records to pull up the desired found set for exporting.

          • 2. Re: XLS export in IWP
            MilutinPetrovic

                 Thats exactly what I need! Now how do I do it!? :)

            • 3. Re: XLS export in IWP
              philmodjunk

                   If you had described your database in more detail, I could have responded in more detail. I'll have to keep this example very general.

                   I will assume that you have a serial number field, __pkPrimaryKey that uniquely identifies each record in your table.

                   There are two basic approaches to building the list of IDs and both options have trade offs.

                   Copy All Records is fast, but destroys any data previously copied to the clipboard.

                   A looping script that builds the list preserves any previously copied data but is slower.

                   If your typical found set is fairly small (several 100 say), I'd use a looping script, but use Copy All Records in cases where the typical found set is much larger.

                   With Copy All Records, you create a layout where __pkPrimaryKey is the only field on the layout but the layout is based on the same table occurrence as the layout where your user has pulled up the desired set of records. The Script then changes layouts to this layout, uses Copy All Records to copy the data and then changes to a third layout where the script creates the new record and pastes the copied data into a field in your "print" table. The copied/pasted data will be in the form of return separated list of values--which can be very handy at the other end of this process.

                   A looping script might look like this:

                   Go to Record/Request [first]
                   Loop
                      Set Variable [$IDList ; List ( $IDList ; YourTable::__pkPrimaryKey ) ]
                      Go to Record/Request/Page [next ; exit after last]
                   End Loop
                   Go To Layout [Print]
                   New Record/Request
                   Set Field [Print::IDList ; $IDList]
                   Go To layout [original layout]

                   I imagine that you'll also need a second set field step in there that identifies the recipient, perhaps by recording an email address.

                   On the other end, your timer controlled script can use Go To Related Records to recreate this found set if you define this relationship:

                   Print::IDList = YourTable::__pkPrimaryKey

                   This will not reproduce a particular sort order so you may need to sort your records after the Go to Related Records step.

              • 4. Re: XLS export in IWP
                MilutinPetrovic

                     In more details:

                     I have a main table with around 70 fields. And I need to export it, in a found set, via the IWP.

                     What I have thought to do is to crate a temporary table called Print in which the Primary keys from the Main table found sets would be stored, once the user click on Export button. Then, the robot script (OnTime) will pick it up as a "export request", enter the main table, use the IDs exported to Print table to perform find, and export the table, and than add it to a container field.

                     Of course the Main table has a primary key field called ID.

                      

                     Here is a small chart which would Maybe explain my idea better!

                • 5. Re: XLS export in IWP
                  philmodjunk

                       This is consistent with what I have previously suggested. What you are calling "main IDs" are what I am calling a "Primary Key".

                  • 6. Re: XLS export in IWP
                    MilutinPetrovic

                         OK, from what I understand, the "List" function will make an array of IDs that are needed for export, and then I can use that array to qery the main table for records. How do I use that array to perform find?

                    • 7. Re: XLS export in IWP
                      philmodjunk

                           It's not really an "array" its a return separated list of values. If you examine the field in the Print table that is set with the list built in the variable, you'll find that you have a long list of IDs each on a separate line in the field.

                           You need to take another look at my previous post where I described this solution. No find is performed. I am describing using a relationship and Go To Related Record to bring back the found set that the user had when they logged this "print" request. A looping script could be used with such a list of values to perform a find, but Go To Related Record can do this in a single script step so it makes for a much simpler way to do this.

                      • 8. Re: XLS export in IWP
                        MilutinPetrovic

                             If I could borrow Your mind for couple of hours, I would not have the headache I have now... I'm growing to like this filemaker, but I still have miles to go! 

                             My typical set is between 100-150 to up to 2000... So then it would be, according Your previous post, better idea to use copy all record... I have no idea how to use that being that I never did! I will try this now!  I understand the copy set and paste to Print layout, the only thin i still have issue with is the recreating the found set via the relation ship? how the relationship should look like? One-to-one I suppose?

                             I don't know if "awesomeness" is a word in English, but Yours is epic... I don't know how to thank You friend!?

                        • 9. Re: XLS export in IWP
                          philmodjunk

                               Two things to keep in mind with the Copy All Records script step:

                               1) Copy All Records copies data from all fields on the current layout for all records in the current found set. The data in the fields are separated by tab characters, the records are separated by return characters. SInce we just want a list of ID fields, you thus need a layout with just that one field on it.

                               2) Paste will silently fail to work unless the field being pasted into is present on the current layout.

                               Also, My "100-200" records is just a rough rule of thumb. You may want to try both approaches and see if large found sets are produing unacceptable delays or not when you use a looping script.

                               For the relationship, I posted what you need earlier. Heres the same info in more detail and subsitute the table and field names you actually use in place of mine:

                               Print-------<YourTable

                               Print::IDList = YourTable::__pkPrimaryKey

                               If the notation is unfamiliar, see: Common Forum Relationship and Field Notations Explained

                               Then, from a layout based on Print, this script step:

                               Go To Related Record [Show only related records; From table: YourTable ; Using layout: "YourTable" (YourTable)]

                               will bring up the original found set of records.

                               This works due to the fact that a return separated list of values functions in a unique manner: Any one of the listed values may match to a value in the field on the other side of the relationship. So if you had a list of 3 ID numbers 1, 2 ,3, it would match to a record with __pkPrimaryKey = 1 or a record with __pkPrimaryKey = 2 or a record with __pkPrimaryKey = 3 in the other table.

                          • 10. Re: XLS export in IWP
                            MilutinPetrovic

                                 Im giving it a go right now!

                            • 11. Re: XLS export in IWP
                              JOMUSTILLO

                                   Hello Milutin ,  could you post the entire script  so that I can test exporting via IWP. Thank you so much.

                              • 12. Re: XLS export in IWP
                                MilutinPetrovic

                                     I got the solution, but it is a series of a couple of scripts. There is a "robot" on time script which detects is there a request for export and it performs it. The process looks like this:

                                     1. First, the script checks if there is an export in progress and if that is a case, sends a message that You need to wait for a minute.

                                     2. Then script transport the series of ID-s from the found set in to a temporary table and it makes a note in "Export order" table that there is a request for export.

                                     3. Then the On-Timer script (which runs every minute or so) picks it up and starts another script that, using the Related records script step, creates and array to create export with.

                                     4. Next step creates the XLS file in the Temp folder, and than inserts it in to the table, with the information on Date/Time of creation and the user that requested the export. 

                                     5. Final step is to clear all of the temp tables related to export, and that's it.

                                     There was an idea to create a script which would delete the file after couple of minutes, but I was to lazy to do it! :)

                                     There is no point in pasting scripts here, cause there is a few different steps to it, but I will do my best to upload a sample file You can use...

                                • 13. Re: XLS export in IWP
                                  JOMUSTILLO

                                       That would be great! Thanks again

                                  • 14. Re: XLS export in IWP
                                    MilutinPetrovic

                                         OK, here it is, back by popular demand! :)

                                         Being that the database was originally done in Serbian, and that I revoked the original database for the purpose of this sample, if something remains unclear because of the language or any other purpose, feel free to ask, I would be happy to help! The link for the file is in the bottom of the post. I had to use Drop Box, being that the Forum do not allow file hosting. I am not 100% sure Drop Box works when my computer is off-line, if not, PM me You email, so I can send it to You... I gotta go now, it's 1:34 am now here! :D

                                         Just a few reminders:

                                         • Database runs a Start Up script, which in turn runs the On-Timer script, which in turn runs the export and all related processes.
                                         • There are 2 user names, one being the Admin, and it runs the database, other is qwe, which is used for testing in IWP.
                                         • If You want to work out the Samples internals, You would have to stop the On-Timer script. The simplest way to do that is to go to the
                                           File > File Options > Perform Script (Tick Off).

                                          

                                    Big credit for this goes to the PhilModJunk! His help was essential in the development of this solution! Thanks again pal...

                                    http://www.dropbox.com/s/eh5u2h43rz46l7q/Sample.fp7

                                         I hope this helps You... Cheers!

                                    1 2 Previous Next