9 Replies Latest reply on Jun 6, 2017 1:57 PM by tleitzke

    Auto-fill new database with data set

    tleitzke

      On a simple calculator (that fully functions) for bidding out costs on HVAC systems, I want to have a set of "materials" (portal) auto-fill with a pre select set of data (as shown in the image at the bottom of this post). I know how I can manually go and selectively put in each and every item- but that would make a large script, and the user would not be able to modify easily without knowing how to use the scripting and more than they need to learn (I can trigger the script, I just need help on the most beneficial process to make it dynamic and simple for user and me)

       

      Capture.PNG

        • 1. Re: Auto-fill new database with data set
          erolst

          Rather than having long scripts, have short scripts that utilize the data structure ... that's the spirit

           

          As I understand it, your challenge is twofold:

           

          1. Select material/services and and a default value to be included into a new calculation set

          2. Have a UI that allows a non-technical user to change these settings

           

          In a very basic form, that could be a list layout with a checkbox "Include in calculation", and a dedicated field "Cost in calculation" (if it deviates from the usual cost/price).

           

          Whenever you want to create a new Calculator, create a new Bid (that's what it actually is, right?) record, import the checked records only, along with their cost values, into the BidLineItems table, and give them the correct foreign key to relate them to the bid.

           

          It may get more involved in a multi-user scenario, but let's burn cross that bridge when we come to it.

          • 2. Re: Auto-fill new database with data set
            clayhendrix

            How many different sets of materials are there? A few options come to mind, but it seems important to know approximately how many different sets of materials there are: 12 or 1,200.

            • 3. Re: Auto-fill new database with data set
              tleitzke

              There is only this 1 set of materials.

               

              Having a checkbox set list is one thing I was thinking of, However, I need this to be as simple as possible (and several technicians who will be using it did not like it). This is a full view of the whole calculated portion. Each "calculation" is its own special id connection already, and we can only afford local network or FMGO usage.

               

              Capture.PNG

              • 4. Re: Auto-fill new database with data set
                erolst

                tleitzke wrote:

                Having a checkbox set list is one thing I was thinking of, However, I need this to be as simple as possible (and several technicians who will be using it did not like it).

                Did these technicians have anything constructive to contribute?

                 

                What would be their favourite mode of selection?

                 

                tleitzke wrote:

                Each "calculation" is its own special id connection already, and we can only afford local network or FMGO usage.

                Im not sure what you're trying to say here.

                • 5. Re: Auto-fill new database with data set
                  tleitzke

                  They only said they liked how easy it was to add extra expenses, and equipment (which I dropped the filtering of brand/type because there may be case that a unit needing to be calculated could not be on the list or of our common brands)

                   

                  We cannot afford to host online connections with Filemaker, so every app is local to wifi/ethernet network, or on the device as its own.

                  • 6. Re: Auto-fill new database with data set
                    tleitzke

                    I am still looking for some solution, and it has come up that pretty much several (very similar lists. 95% same, less than 15 items)

                     

                    An idea I had for a solution was to have a table for jut listing out all of the possible extra costs, and have a checkbox with all the areas (with a checkbox to apply to all within that set- save a smidgen of time). And then have something like:

                     

                    For [row] in [table]{

                       if ([row][type] == "X" or [row][type] == "all){

                           insert [row] into [table 2]

                           Set [table2] new row CalcID to [Calculation ID]

                        }

                    }

                     

                    I am trying to think of the simplest way to do it, and I have no clue. It is fairly badly needed to work as the boss likes the format and thinks it is far better than the old way (excel spreadsheets. And far better than his solution of putting every match system on their own separate excel sheets to save a good 2 seconds)

                     

                     

                    EDIT:

                    I did get it working with this script, but I do not know if it can be improved, or I am doing something hazardous.

                     

                    Capture.PNG

                    edit 2:

                    Because the $BidType was done using check boxes, I has to switch to use TextPattern() in the if statement, because it would skip some because the multiple values.

                    • 7. Re: Auto-fill new database with data set
                      erolst

                      tleitzke wrote:

                      Because the $BidType was done using check boxes, I has to switch to use TextPattern() in the if statement, because it would skip some because the multiple values.

                      If you check multiple items in a checkbox, you get a CR-delimited list of values. Obviously, no single type in the looped records is identical to a list of selected types; what you want is to check whether that record's ID is in the list. To do that, don't use PatternCount(); there is a function tailor-made for this:

                       

                      not IsEmpty ( FilterValues ( $bidType ; StartingExpenses::Type ) )

                       

                      which returns true if Type is in $bidType.

                       

                      Having said that ...

                       

                      tleitzke wrote:

                      I did get it working with this script, but I do not know if it can be improved, or I am doing something hazardous.

                      No to the latter, but as to the former ...

                       

                      Try creating a relationship like

                       

                      Calculation::Calculation Type = StartingExpenses::Type

                       

                      then use Go to Related Record. Now you have the records matching the type selection.

                      Use Extend Found Set to add to that set the records of type "All".

                       

                      Use Export Records and create an export file in the Temp folder with the three relevant fields.

                      Switch to the Extra Expenses layout and import. Now go to the Calculator layout.

                       

                      That should be faster and shorter, even taking into account that you need to add some safeguarding measures.

                      • 8. Re: Auto-fill new database with data set
                        tleitzke

                        Speed wise, it was happening in an unnoticeable small amount of time between start and finish of the script.rel

                         

                        I want to use the relationship method you suggested, but it does not seam to work properly- only bringing up a single item from the list. Not even "Extend Found Set" is doing anything (and I know for sure I set several to "All").

                         

                         

                        Capture.PNG

                        (I get an error of no matching records found when ran)

                         

                        I think it may not be finding records because starting items can have several values from checkbox, but not others- so trying to match set [a] to set [a,b]  ; which [a] != [a,b]

                        • 9. Re: Auto-fill new database with data set
                          tleitzke

                          I wanted to be more on the safe side, but this is the only thing working for me currently (updated for allowing to skip a giant step to not include any to start from nothing). So I guess this one is closed.

                           

                          Capture.PNG