14 Replies Latest reply on Sep 15, 2016 3:35 PM by kaotishe

    Not even sure where to start this one.

    kaotishe

      I recently developed some point of sales software with WordPress to replace the paper order forms I had been using in the past. The new software creates an end of event CSV file which I want to now import into FileMaker. If I just "create a new solution" out of the CSV what it gives me is not very useful for a lot of reasons. One is that I single event's sales gets turned into multiple records. But I need all of those records to relate back to the original client where the event was hosted. But the second is when I import a new event I don't see a way to do it that does not create an entirely new table instead of bringing the sales data for the new event into the existing sales data table. From there I will need it to generate several sub reports specific to the generation of products (but I don't anticipate much of an issue with that) and finally I want to then relate all of this back to the original client table that I use for scheduling events so I can easily see much more specific numbers then the information that i am currently login into a child table associated with that existing solution.

       

      So, Ideas on where to start?

        • 1. Re: Not even sure where to start this one.
          philmodjunk

          Use import records to import the data from the csv file into FileMaker. This will allow you to repeatedly import data and each new import will add new records to a table in your FileMaker database.

           

          I single event's sales gets turned into multiple records.

           

          This will still happen, but that doesn't make this process unworkable. Most sales transactions should be data divided into multiple records--typically, you have a single invoice record that identifies the customer, date of sale, etc and then a set of related records, one for each item or service purchased in that sales transaction.

           

          Exactly how you get that data into coherent form in the FileMaker database will depend on:

          a) the way the data is output to the csv file, what goes into each row and column?

          b) The way you design your FileMaker database

          c) what you need to do with this data once it's been imported into FileMaker.

          • 2. Re: Not even sure where to start this one.
            gdurniak

            Which Version of FileMaker ?

             

            From the Menu,  File / Import Records / File  does allow you to select an existing Table  ( check FileMaker's "Help" )

             

            You do not want to "Create New Solution" each time

             

            Once the records are imported,  you may need to write a script to then set a "Key" field, that relates back to a Client Record ( which might be a challenge,  if you are just learning how to import  :-)  ), or perhaps to combine multiple data records into one record

             

            It is a bit tricky, but possible

             

            greg

             

            > when I import a new event I don't see a way to do it that does not create an entirely new table instead of bringing the sales data for the new event into the existing sales data table

            • 3. Re: Not even sure where to start this one.
              kaotishe

              Ok I did find how to put multiple CSVs into the same solution. However I don't really see what to do next to organize the data into a useful form. Instead of having 2 CSVs for two different jobs I now essentially have one but all of the rows are individual records. I know there has to be a way to use filemaker to make this data more usable, but right now what i am getting is less usable. Do i need to create a related table that can group these records by client ID so I can then start distilling the data down into types of products sold at that job site?

              • 4. Re: Not even sure where to start this one.
                philmodjunk

                We can't tell from your posts how your data is organized in the CSV and thus what you get when you import it. What does each row of imported data represent? What do you have in each column?

                 

                Multiple rows of data and thus multiple records of in FileMaker, if each row represents a different item purchased, may be a very good form for your data and does not preclude getting something where you also get one record for each complete sales transaction--if that's what you want here, but there are also ways to present your multiple records in a summary report that emulates that result and which is easier to produce.

                 

                So please describe your csv export in more detail so that others can better advise you.

                • 5. Re: Not even sure where to start this one.
                  kaotishe

                  I am a photographer and each row of data in the CSV is a sold image. About 3/4 of the columns are actually only relevant to how the WordPress form operates, but the other quarter is made up of a variety of size and art options for the image. Of course not all images have the same options selected. I need to be able to organize image numbers with the relevant size and art options as well as have a coalesced sales report to go with the original client database so I can keep track of which clients are worth rebooking and which ones can be given lower priority for rebooking. Now I did have enough forethought to include a space in the WordPress form to use the Filemaker client ID number - so that does show up in the CSV with each of the orders placed - along with the date information. So those two pieces can be used to tie the information back to specific scheduled photoshoots in FIleMaker and the relevant client.

                   

                  So basically on import I need a script that will list

                   

                  1) which photo numbers are ordered

                  2) which of these have background art

                  3) which are being printed as magazine covers, trading cards, or memory mates along with the relevant data for the fields on said items.

                  4) etc... were are a few other options but once I know how to get the information in a useful packet I can replicate it for the different items. Also on all of these I need to have an easy way to get tab delimited files for each class of item i.e. a tab delimited file for trading cards which lists each image number along with student name belt rank etc... so that it can be fed into photoshop variables to put the relevant information into the cards - eliminating typos on our end - and speeding up that process.

                   

                  Ideally I would like a script that will pluck the sold images out of the main image folder on the computer and place them into a sold image subfolder (I don't know if this can be done).

                   

                  But I also want the build down sales data to go back to the original client data in the database. When everything was on paper I was only tracing total number of images sold and total sales with the client info - it gave me enough of an idea to know who to prioritize each year. But now that all of the sales information is digitized I also want to track which print sizes are selling and which art options people prefer so I can use that information to better tailor what option I offer people in the future.

                   

                   

                  And since someone asked above I am using FileMaker Pro 15.

                  • 6. Re: Not even sure where to start this one.
                    philmodjunk

                    Seems to me that you should keep those rows of imported data as separate records--exactly as your Import Record step produces. You can link those records back to a table of customers by the customer number so you can get a "view of the data"--either as individual items sold or in an "aggregate" form via the relationship.

                     

                    If there's a column that uniquely identifies each overall sales transaction or if a given set up imported records are limited to just those items purchased on a single "invoice", then you can do further grouping of the sales data either by customer, invoice or both in a summary report and a follow up script that runs after import can create invoice records if you actually need them

                     

                    1) which photo numbers are ordered

                    That would seem to be info listed directly from your table of imported records. A relationship or a find can list all records for a customer, for a specific sales transaction, a date, a category of photo or some combination of those and possibly other options depending on the data in your database and imported from the CSV file.

                     

                    2) which of these have background art

                    3) which are being printed as magazine covers, trading cards, or memory mates along with the relevant data for the fields on said items.

                    4) etc... were are a

                    Can't tell that from the info provided so far. In 2, 3, etc, is this info stored in your database or imported from the CSV file?

                    • 7. Re: Not even sure where to start this one.
                      kaotishe

                      All of this is information coming from the CSV file.

                       

                      Essentially each row of the CSV is a single invoice of a specific image. If a person orders two separate images WordPress invoices that individual for each image and each of those lines then breaks down in to the specific print sizes and artwork that is applied to that image. So essentially one line in the CSV has the student name, the parent name, the image they purchased, the sizes of prints, background art, trading card/magazine cover/memory mate info IF these are purchased... along with a bunch of columns that as I mentioned above are necessary for the form to work online but really just take up space once the CSV is exported for the artist. Although some of that information will be useful in aggregate form for tracking overall sales at a given shoot.

                       

                      However this listing of the information is not terribly efficient in the work flow for processing the images.

                       

                      It is faster for the artist to do the basic editing across all of the purchased images first. Then to go through and add the backgrounds to the photos that have background art and then from there go back and add the magazine or trading card templates.

                       

                      My initial thought had been to create sever versions of the CSV that would giveth artist each portion of information that was relevant to the step of the work flow that they were in. This of curse meant creating each of those from the CSV each time there was a photoshoot. Then it occurred to me that I should be able to script FileMaker to do that... But once I started to import the information into FileMaker I realized I had no idea how to actually do that...

                      • 8. Re: Not even sure where to start this one.
                        philmodjunk
                        Essentially each row of the CSV is a single invoice of a specific image.

                        Seems like we are using "invoice" very differently from each other. An invoice generally lists multiple items--such as multiple photos all purchased by a given customer in a single sales transaction. Line items then list each photo and the information relevant to that one specific photo (number of prints, background, line item cost....)

                         

                        Correct me if I'm wrong, but what you are importing are those individual line items, with one row of data for each item purchased. The details that are relevant to that one photo are listed in other columns of that one single row.

                         

                        It seems to me that the one record per photo structure that you are getting is exactly what you need for your work flow. Of course, for each step in the process, you don't have to show columns of data that aren't relevant to that step in the process.

                         

                        So I'm still at a loss as to what you want that's different from what you are actually importing.

                        It is faster for the artist to do the basic editing across all of the purchased images first.

                        What does "All" mean? All the photos from a given sitting, a given school, all of those for a given student?

                         

                        In every of the above cases, a simple list view layout can show "all" those records so that the artist can quickly move from photo to photo in that group--whatever group that happens to be.

                        • 9. Re: Not even sure where to start this one.
                          kaotishe

                          No, we ARE talking about invoice in the same way. Unfortunately right now if a single person orders photo 005 and photo 009 they are invoiced twice. I want to change that, but we will have to build another layer of functionality into our WordPress form which is not there yet. If the person pays on-line with PayPal they make two payments, if they pay me at a shoot with a check or whatever they can make one payment, but they ARE invoiced twice.

                           

                          The first photo 005 may just have background 010 added to it and be printed as an 8x10 which gives that invoice two line items.

                           

                          And the Second photo 009 may be printed as 2 5x7s and a pack of trading cards - again two line items but they are not the same and the work flow between the two images ends up being different.

                           

                          The Artist is not sitting in front of a copy of FileMaker - we don't even have an office. I need to be able to give her easily understandable packets of information (instead of just giving her the CSV, which is what I have to do right now) that correspond to the different steps in the work flow.

                           

                          What I need to know is how to script FileMaker to export these different packets of information. Ideally something that will occur automatically on import and that will exclude all of the information from previously imported photoshoots.

                           

                          Now the first step in the work flow is for all purchased images to get basic editing, and yes that can just be a list export of image numbers purchased - but ideally what it will do is reach into the image folder on my computer and copy just those images into a subfolder of purchased images. Then she can import just those images into Lightroom and do the basic edits.

                           

                          The next step needs to create a tab delimited file of images purchased with a background added and the corresponding background number. Then this can be imported into Photoshop and the images will already be paired with the correct background and she will only need to do the masking steps that the computer can not do.

                           

                          Then we need a tab delimited file of images with trading cards and the corresponding date that goes with them, student name, year, martial arts style, etc that again can be imported into photoshop and the trading caed layout for each image can be pre-filled eliminating typos and a lot of repeated work that the computer can do faster anyway... The same for magazine Covers and Memory Mates

                           

                          All of this information is IN the CSV - I just need to know how to have FileMaker get it out.

                          • 10. Re: Not even sure where to start this one.
                            DrewTenenholz

                            kaotishe --

                             

                            From what I know about WordPress, I can see how what you are doing has you rather confused.  WordPress certainly has its own way of organizing/storing/exporting data that you don't have 100% control over.  Are you using the WooCommerce shopping cart system or something else?  Did you build your own web form and POS system, or use something pre-built?  This helps inform us as to what options you might have to clean up the data coming out of WordPress before it reaches FileMaker.

                             

                            If you have the sense that what is included in the export is all the information you need (plus some extra data you don't), then here is a more or less general practice that is used for repeatedly importing records into a FileMaker solution.

                             

                            1) Import -- Set up a table and layout whose sole purpose it to import the records from the CSV file.  Scripting a button or menu to go to that layout and repeat the import process (allowing you to select the current import source), is almost trivial in FileMaker.

                             

                            2) Post-Process -- Once you have the records that were imported, you might want to manipulate the information in a few ways to make it easier to understand.

                             

                            You might want to combine data from two adjacent rows into a single row.  If your data is regular enough, the 'first' line will be easily identifiable from the 'second' line, and you can use scripting to patch together the information from two lines into one new line in a new table.

                             

                            You might want to create an ORDER (with customer name), and LINE ITEMS (taking three or four lines and putting them in an related table to the order).  You might be grabbing the name from the first row (or second or third, it really doesn't matter because it is all repeated on every row), then going back to the first row to create the first line item and the second row for the second line item, etc. 

                             

                            You might not need to combine rows or create related records, you might just want to copy certain fields out from the import table into a new table but only copy some of the fields to let you breathe easier and focus on the things you'd like to do.

                             

                            All of these options can be scripted, so you click once to import, once to post-process, then move on your merry way.  (And, you can then delete all of the lines from the import table, since you've captured them in the format you want.)

                             

                            Yes, if the rows have everything you need, it is certainly possible to import and use the records directly in the import table using a layout where only the necessary fields show, but I like to separate the two processes out to make sense of the work flow.  Plus, if you ever grow larger and use FileMaker Server, having the data import and post-processed separated from the rest of the work you do means you can set up FMServer to automate the entire import/post-process piece and send you an email whenever a new import gives you more work to do.  In fact, you might be able to skip the CSV export and use ESS to directly interact with the WordPress system, but I'd say that's pretty advanced for right now.

                             

                            That's a general plan.  If you think it will work for you, there's a lot of help available here for setting up a script to loop through the records and 'do stuff' to them.  Just ask.

                             

                            -- Drew Tenenholz

                            • 11. Re: Not even sure where to start this one.
                              kaotishe

                              Drew, thanks for your response.

                               

                              The order form was built using Formidable, and it was not built by me. I had no experience with WordPress prior to July 1 of this year. And really I only have limited experience with FileMaker though I have been using it about 5 years. Everything I need is in the CSV however there are also about 75% more fields in the CSV then I need once the information comes out of WordPress. They are needed for the online functioning of the form but not in the post processing of the images.

                               

                              I don't know if you saw my post just above yours as they posted at almost the same time, but it gives a more in-depth explanation of what I need to do with the data now that WordPress has gathered it. There are more things I need to do as well - in the past I have been putting data from photoshoots into 2 different spread sheets to track expenses and how much to pay employees as well as into FileMaker to track client sales from year to year. All of that can also be extrapolated from the CSV as it all starts with that collected data. Which would free me up from a to of repeated data entry. But I don't know where to begin. The imported CSV table does not seem particularly useful... At least not anymore useful then it is before it is imported. I am still faced with going through fields and getting the information I need, only now once I add as second shoot to the database I will have to photoshoots to comb through, and then three, four, five, etc...

                              • 12. Re: Not even sure where to start this one.
                                philmodjunk
                                What I need to know is how to script FileMaker to export these different packets of information. Ideally something that will occur automatically on import and that will exclude all of the information from previously imported photoshoots.

                                So now that you've imported into FileMaker, you want to turn around and export it again? In what format?

                                 

                                To repeat, it appears you have one record for each photo purchased as evidenced by your most recent description where you have one photo printed two ways that appears as two rows of data in the csv file.

                                 

                                When it comes to presenting your artist with this info, you simply need to do finds to pull up the rows you want the artist to see followed by a sort to group them in a manner that best suits what you need. When you export or pull up this info on a layout in FileMaker, you have complete control over which columns you display on the layout or select for export so you can simply omit the extra fields from either a layout (if the artist uses fileMaker) or your exported data.

                                • 13. Re: Not even sure where to start this one.
                                  DrewTenenholz

                                  kaotishe --

                                   

                                  No, I didn't see your response, but I did predict some of your issues.

                                   

                                  You are definitely going to want to import in to a holding table, then post-process into other tables.

                                   

                                  From the data in the CSV you describe, I can see tables for:

                                  ORDER (a combination of all the 'invoices' made by the same customer contained in the import)

                                  ITEM (the record of each purchased item from the CSV, linked to the ORDER)

                                   

                                  You can build the ORDERS by looping through the records from the CSV making one per customerID.

                                  You can build the ITEMS by looping through the records from the CSV and combining separate lines for the items and their options into a single record.

                                   

                                  You link the ITEMS to the ORDERS by capturing the ORDER ID you create for each customer and adding that to the ITEM records you create for them.

                                   

                                  Once the data makes sense, it should be pretty simple to group similar ITEMS and export them to the different work flows (the 8x10s, the trading cards, etc and maybe assign them to different people giving each mini-grouping a projectID )  You can easily generate a well-formed export which gets sent to the worker. Tagging each item with the employeeID tracks what they will get paid for that item.  (And what you charge. And whether that item is already paid or not.)

                                   

                                  At this point, you'll also be able to see which orders are paid, part-paid, and unpaid and generate bills accordingly.

                                   

                                  As for grabbing specific images from a pre-existing FileMaker library and including them as part of the data sent to the worker, that's a bit trickier.  Not insurmountable, but FileMaker is a database program with a lot of fun add-ons, not a file/folder manipulation system.  So, if you have a folder full of images , you can create a table for them and give them all IDs. (I suppose you have this already from what you've been saying.)  Exporting the images to a specifically named folder (say a 'projectID') for a task, is possible, and there are a bunch of ways to do it.  But, if you want to automate the process whereby three images and three backgrounds are dumped into a project folder which is then .zipped and automatically attached to an email to the correct employee, you are going to have to learn to use at least one FileMaker plug-in to do that.  Not impossible, and a huge time-saver, but not a weekend project either.

                                   

                                  Does this start to make sense of what you have and what you want to do?

                                   

                                  -- Drew

                                  • 14. Re: Not even sure where to start this one.
                                    kaotishe

                                    Drew,

                                     

                                    I am not sure how to import into a holding table and do the post processing. I feel like I should be able to do this - I wrote a script which combs through my clients table finds upcoming photo shoots and then emails information to Mark for sending out info to the different locations - it even ignores the entries fro the information that it has already sent before. This has got to be a similar process.