7 Replies Latest reply on Jan 25, 2017 2:07 PM by philmodjunk

    New to Filemaker Questions About Layout Management

    kbs756

      Hi,

       

      So I'm messing around with a Demo of FileMaker Pro and trying to figure out how to do some things and it may just be that I'm not sure what they're called and searching for the wrong thing but I would appreciate if I could be pointed in the right direction on certain things.

       

      I know this is a lot but just hoping someone can at least point me in the right direction with these topics or alert me that I'm approaching it wrong.

       

      - For one I'm wondering if I can make a custom start layout based on the user that logs in to select the relevant layouts they can access even if for example there is no active relationship between certain tables or sets of data. Based on what I've read a layout is always supposed to start with a focus on a certain table does this matter for something like this? or can I somehow define a default layout based on user type and privalidges that doesn't have to be referenced to a table and just fill the interface with buttons which go to a certain layout?

       

      - Second, I'm probably blind but is there somewhere I can limit an accounts access to certain tables ... or even to certain records in a table the account has access to? For example say I have a set of retail customers and wholesale customers ... and I want to keep their info different as I don't want normal employees not involved with wholesale to be able to access and contact my wholesale customers. Additionally within wholesale customers if I have different salesmen and women, who have been assigned to or brought in different wholesale customers I don't want them to be able to access a different sales persons customers. Is there a place where I can build in this type of granular security?

       

      - Third- I was also wondering about scripted imports and exports from excel files and if its possible to map the values from say an order file import filled out in excel to the various component tables that it would have to populate. Say for example I sell on eBay or an other marketplace and they send me a form of data for my orders which could now include customer info, seller info, shipping type, shipping date, etc ... so I would want the script to either link the data to pertinent contacts in related tables or if not present actually create the customer and such records. Also would not want to have to constantly map the set format and rather as long as its a set format read it correctly.

       

      - Forth - I was also hoping to be able to export certain items and there are very specific formats for export that I would need to be able to achieve to for example export items for listing to an eBay listing solution. For this variation listings often contain one line which is the master record and trailing lines which do not populate a lot of the data which are the variations and in this order as well as formatting are very important. Is it possible to if i say select items to list from my products ... and then select variation groups ... to somehow script this type of structure during an excel export as I would be able to in an excel macro? Trying to do everything under the hood of one solution rather than being all over the place

       

      Fifth - What would be the best approach to storing data on all the multiple variations I have ... for example one of the products I do is jewelry.

       

      So I have a designs table which has the pertinent data about the design

      a Models table which has pertinent data about the multiple models which are under the same design

      and then say the model takes gemstones ... I could have 10 different gemstones ... in 20 different qualities

      I can also have many different metals for the same object to be created in.

       

      So one I would have to manage the different variations consistently to be able to reference them correctly and consistently. And 2nd I would want to be able to price them automatically, but also not override the price in say invoices and such after they had sold

       

       

      Anyway thx again in advance to anyone who can direct me in the right directions. I know it sounds like I'm trying to run before I can crawl, but would appreciate the help as right now my business functions on a ton of separate excel files and a lot of paperwork and I'm trying to streamline it

       

      Thanks!

        • 1. Re: New to Filemaker Questions About Layout Management
          SteveMartino

          Yes all of those can be accomplished.  But it's going to be based on your ability to learn how to do it.   I would start with trying to learn FileMaker, then start building your database.  If you just jump right in, you will find yourself most likely having to rebuild the entire database.

          What you want to accomplish is very complex, but easy to do once you understand the basics of Security, Scripting, and relationships.

          I would start with:

          -FileMaker Training Series-both free and Advanced

          -FileMaker, The Missing Manual (for your version).

          I feel the best training video out there is this one:

          The Philosophy of FileMaker - Bundle

           

          Mostly all the things you need to do are done with Security and Privilege Sets.  In there you can decide right down to who can look at what and where.

          Security is probably one of the hardest concepts to grasp.

           

          I would recommend the training first.  Then as you're building the database, pop back on and ask specific questions.

          2 of 2 people found this helpful
          • 2. Re: New to Filemaker Questions About Layout Management
            kbs756

            Thanks for your response could you at least let me know what the proper terminology or keywords are for the questions i asked ?

             

            appreciate the link to the tutorials

            • 3. Re: New to Filemaker Questions About Layout Management
              philmodjunk
              For one I'm wondering if I can make a custom start layout based on the user that logs in to select the relevant layouts they can access

              Take a look at File Options... There's an OnFirstWindow script trigger that can be used to run a script when the file opens. This script can check the user's account or privilege set or list of extended privileges to determine which layout to bring up as their initial layout. In addition, you can put in place navigation buttons that move your user through different layouts but, because you are using a script, the precise layout brought up can be different for different users and different devices.

               

              is there somewhere I can limit an accounts access to certain tables ... or even to certain records in a table the account has access to?

               

              This is something that you can set up inside Manage | Security. WHen you edit or create a privilege set for an account, you can select a "custom" option for Records. Within the dialog that opens for that, you can select a table and a class of actions (view, edit, create, delete...) and select the "limited..." option in order to set up a Boolean calculation that controls which records are accessible to a given user when they open the file with an account that uses that privilege set.

              Third- I was also wondering about scripted imports and exports from excel files and if its possible to map the values from say an order file import filled out in excel to the various component tables that it would have to populate.

              Start by researching the Import Records menu option, then look up the script step of the same name. It is often necessary to import into a "staging table" that replicates the row and column structure of your excel file followed by a script that then moves the data into your FileMaker tables if they are organized differently from that of the Excel file. (one Excel row = 1 record. 1 Excel Column = 1 field in that table.)

               

              I was also hoping to be able to export certain items

               

              And this can be the same as importing but in reverse--however there is an option that would produce one row of data for a parent record followed by a flexible number of rows of data from a related table that you may want to investigate.

               

              What would be the best approach

              There are a lot of details missing here that can affect what turns out to be the best approach, but in general, when you have variable data, it usually comes down to setting up tables of related records so that you can create as many or as few records to list the details needed in each circumstance. What you describe here sounds very similar to how a manufacturer sets up a BOM--a "Bill of materials" or "recipe" for describing what is needed to manufacture one unit of product--whether that be a frozen pie, an automobile or a piece of (maybe) custom jewelry.

              1 of 1 people found this helpful
              • 4. Re: New to Filemaker Questions About Layout Management
                kbs756

                Thanks for the help on this but would you be able to at least direct me in the right direction on where I can find out how to create staging tables ...didnt see it explicitly mentioned in the tutorials which were referenced before

                 

                As for the bill of materials ... I think i get how that works now but if there is an example of such implementation out there please let me know thx

                • 5. Re: New to Filemaker Questions About Layout Management
                  philmodjunk

                  A "staging table" is just a table used during the import process. It would have the row and column structure that matches that of the files from which your data is imported. You'd then use a looping script to move the data from the this table into specific tables/records/fields in your FileMaker solution.

                   

                  Example:

                  Say an "invoice import" looks like this:

                   

                  Invoice #123 | 1/25/2017 | Product ID 1 | Qty 1 | Product ID 2 | Qty 2

                   

                  In your FileMaker database, you'd want to put the first two columns (Invoice number and date) into a new record in your invoice table and the pairs of Product ID and Qty columns into multiple records of a related table of line items--one for each item on the invoice. Thus, the structure of the Excel (or CSV or Tab or....) file does not match the structure of your tables in FileMaker and prevents a simple import from being used to pull the data into where it needs to be.

                   

                  But a staging table can be set up with the same rows and columns as your excel file and then you can script a process that uses the data in the staging table to create the needed records with the needed data in your invoices and line items tables. Once that script has been run, you can delete all records from the staging table and you are ready for another import.

                  1 of 1 people found this helpful
                  • 6. Re: New to Filemaker Questions About Layout Management
                    kbs756

                    K thanks for clarifying that ... for some reason I read that as a feature ... I guess I can figure out how to script that once i understand the syntax of how these scripts work on records

                     

                    Also is one just able to create a orphan loading table which is not connected to anything and use it to create a layout that will have navigation to other pertinent layouts for whichever account logged in?

                     

                    Thanks again for your help ... apologies for my amateur questions

                    • 7. Re: New to Filemaker Questions About Layout Management
                      philmodjunk

                      Yes and the table need not have any records. You only need more than that if you need to show data on that layout.