7 Replies Latest reply on May 15, 2013 11:51 PM by MartinBridges

    Sending a request for a quote with text and images as a form that suppliers can edit?

    MartinBridges

      Afternoon All

       

      I have a brief to 'email a quote request form'. Easy peasy, lemon squeezy I thought, but then the client got involved. See image attached of the FM form. It contains text and images.

       

      What they have been doing to date is compiling an Excel spreadsheet. Formatted to look like a form. They insert the images in a cell amongst the text. They then manually email ONE Excel file and get it returned in the same format they have sent it in. All filled in.

       

      1. I can use 2 script steps, ie 'save records as pdf' (save it to the desktop) and then use the script step 'send mail' which automatically sets the email address, subject line and general text in the body etc and attaches the pdf. That's an improvement on Excel... but not good enough for the client as the recipient hasn't a form to fill in. So has to manually copy the text into a form that they create.

       

      2. If I use the script step 'Save records as Excel' it exports ALL fields in the table. Most of which are not needed. So no good.

       

      3. If I 'export records' I can limit the fields that are exported. And you get a xls/xlsx table that can be filled in. Great. BUT... it's as a table rather than a form (which they don't like), and of course you can't export container fields with the images in.

       

      4. I then suggested compiling a script that produces a pdf of the form with the images, AND an xls table of the info, THAT when the script step 'send mail' is reached (which auto enters all the stuff they want in a covering email)... it then auto attaches the 2 files on their desktop... solution!...

       

      Guess what? They were disappointed that FileMaker was more complex than Excel to use!

      And they still didn't like the table format or the separate images.

       

      Any suggestions?

       

      Regards

      Martin

        • 1. Re: Sending a request for a quote with text and images as a form that suppliers can edit?
          DavidZakary

          While this doesn't address the image part of things, this could be an option...

           

          Create a fillable PDF form using Acrobat that looks like your FileMaker layout. Add all of the required fields.

           

          In FileMaker, export your data as XML. You can then create an XSLT file to import the FM XML into the fields in Acrobat. Send that to client. PDF comes back, export data from PDF to XML and import back into FileMaker.

           

          Not particularly easy but it'd work. The initial setup would be a PITA but as long as the form doesn't change it should do the job.

           

          Dave Zakary

          1 of 1 people found this helpful
          • 2. Re: Sending a request for a quote with text and images as a form that suppliers can edit?
            Mike_Mitchell

            Another option would be to produce the form in HTML, then export as a .xls (Excel) file. It would, like the Acrobat option, be a pain to set up, but you'd end up with an editable Excel document produced directly out of Filemaker. Like Dave, I'm not sure how to address the images (I've never tried this method with images), but it might work.

             

            Mike

            1 of 1 people found this helpful
            • 3. Re: Sending a request for a quote with text and images as a form that suppliers can edit?
              MartinBridges

              Thanks Dave and Zakary for your contributions.

               

              Speaking with others it can quickly involve xml, xlsx, php. html...

               

              someone suggested to me a plugin http://www.pdfmanipulator.com which looks interesting.

               

              The client, at present, wants me to explore a simple instant web publishing solution...

              Martin

              • 4. Re: Sending a request for a quote with text and images as a form that suppliers can edit?
                MartinBridges

                Thanks Dave for your help, Much appreciated.

                 

                Why do seemingly simple requests prove the most complex?

                 

                More yet to explore and for the client to reject!

                 

                regards

                Martin

                • 5. Re: Sending a request for a quote with text and images as a form that suppliers can edit?
                  DavidJondreau

                  My suggestion is to disappoint them. Or rather, accept that they're not going to be happy no matter what, so give them whatever's easy for you and tell them that it's the best solution. You can do all this work with XML and PDF Manipulator and they're still not going to be happy.

                   

                  They can use an Excel sheet with no images and no formatting since actually, the Save Records as Excel[] exports the fields on the layout, not the fields in the table. So make a layout with just the fields you want and Save from that. They can import that back directly into the database and eliminate that data entry.

                   

                  Or make a simple web form that just e-mails the results to the company. Or feeds directly into the database (if you have a hosted file and the skills).

                  • 6. Re: Sending a request for a quote with text and images as a form that suppliers can edit?
                    ChristopherReesman

                    This may or my not be helpful but is what I did to create editable order sheets for our sales group to send to customers. It took a little work and my not be the most elegant but it did work for our purpose.

                     

                    1. Create an Excel sheet with the format that you wish to have the data emailed in. Your end form to send out.  This will be your master Excel layout that can have a company logo etc.
                    2. You now need some VBA scripting to bring the data to the master sheet from an open workbook with a given name putting the data where it belongs on the master sheet. Thus filling in the form with the format that you need. I also added using VBA lock all the cells except PO, Date,Notes & qty’s. This prevents changes to part numbers and pricing. I also add the send email script to this code along with when the email is sent it closes the workbook.
                    3. I also added a VBA script to the workbook that runs upon opening of the workbook. It looks to see if a workbook with a given name is open and if not opens it then runs the above VBA script from #2
                    4. Last VBA script is added so that on workbook close the form is unlocked and all data is cleared then the data Excel file is deleted.  The form is now ready for the next use.

                     

                    1. In FileMaker have a button on your form that saves the data as an Excel sheet and script the file name and location to be the same for always.  This is a temp file to be used to fill in the the Excel master form from #1. Script to open automatically when saved.
                    2. When the data file opens the user must now open the master Excel file (usually from recents) and the rest happens automatically.

                     

                     

                    I too wish you could save as pdf in file maker and set fields in the inspector to allow input on the form but this is how I got around it.

                     

                    Is it absolutely necessary to include the pictures?

                     

                    If so these can be stored on a second sheet in the workbook and brought over to the main form with the VBA but it is a little tricky and a pain to maintain if the pictures change often or if you have a large number of pictures.

                     

                    I hope this helps.

                     

                    Reesman

                    • 7. Re: Sending a request for a quote with text and images as a form that suppliers can edit?
                      MartinBridges

                      Thanks for all your imput. Much appreciated.

                       

                      What we've gone with in the end is...

                      1. Create a separate file called Web_Quotes on the server which is an exact copy of the tbl:Quotes' fields and make it available via IWP. This has two tables 1. Users and 2. Quotes.

                       

                      There are 2 layouts attached to tbl:Quotes. One showing all the details and one showing a self join list of all the quotes related by a self join based upon 2 fields,

                      1. login in name which they use to sign in (remember when doing a find for an email address to add double quotes to mitigate the @ operator. So script it as """" & login_name & """" ). This is inherently unique (worldwide) and limits the users to just their records.

                      2. a status field showing 'open' or 'submitted'

                       

                      2. Set up a script to get their email address and transfer all data across to the new file, send an email to the supplier with a web link saying a new quote has been posted.

                       

                      3. In the process it runs another script to see if this account (in manage/security) based on their email exists. If it doesn't it sets one up, auto generates a 6 fig password and sends an 'account has been opened' email with their user name (email) and password and a link to the site.

                       

                      4. Suppliers fill in the web form, which has images and download links. The list view has two buttons 'open' and 'submitted'. When they hit submit a quote it marks it as 'submitted'. It also sets another field as '2download'

                       

                      5. A script runs on layout load in the main database when the Quotes table is opened. This finds all the '2download' files in Web_Quotes and transfers all the details in the main Quotes table. And replaces '2download' with a timestamp.

                       

                      6. Job done and all in FileMaker