1 2 Previous Next 19 Replies Latest reply on Feb 11, 2012 7:44 AM by philmodjunk

    What is similar to a union query in MSAccess?

    ssp.columbus

      Title

      What is similar to a union query in MSAccess?

      Post

      I am not a developer.  I am an interior designer developing a database to specify furniture on large commercial projects.  I have a database in Access that I am trying to recreate in FileMaker.  I am new to filemaker but have been through the training program.

       

      Program:  FileMaker Pro 11 Advanced

      Database Overview: The basics of the database …

      Room table for all the rooms in a building

      Catalog table of all the pieces of furniture

      New Assets table (RoomDetailNew)– items from the catalog are put into a room

      Typical Assets table (RoomDetailTypical) and subtables – items from the catalog are put into typicals (repeating group of furniture items) then the typical is put into a room – example is a typical office has a desk, credenza, bookcase and desk chair.  If there are 100 offices it is a lot faster to select the typical rather than enter the data into each of the 100 rooms.

      Existing Assets Table (RoomDetailExisting) – items from another database of existing assets being reused that are put into a room

       

      There is a room layout with 3 portals to add new assets, typicals and existing assets. (see attached image)

       

      Problem:  I need various reports that combine the data from the 3 main Asset tables.  Although I can summarize the information I need an item list of every item that is in each room.

       

      Desired Result: A table that automatically takes information from other tables to create a list of every item that is in every room. 

       

      In Access I used a select query to get a list of all the items in a typical listed by room then I used a union query to combine the list of New Items, Items from the typicals and Exisitng Items. 

       

      I have 2 specific problems I am trying to solve.

       

      1)   How do I create a table which is a list of each item that is in each room from the typicals?  Table 1 (RoomDetailTypical) that combines the room # and the typical # (entered in the room layout) and allows the quantity to be entered.  The 2nd table (Typicals) is the table which contains the name of the typical and the foreign key for the 3rd table (TypicalDetails) which combines the typical # and the assets that make up the typical.  This table is then linked to the catalog of furniture.  The portal on the room layout shows the list of items in the typical.  I have created a “total assets from typical” table but I'm not sure how to get the data into it.

      2)   Once there is the table that contains all the items in each room from the typicals I need to combine the items from the typicals, the new assets and the existing assets like I did in the union query in Access to create a “total assets” table that list each and every asset in each room.

      I’m guessing I need to write scripts to set the data into the new “total assets from typical” and “Total Assets” but I’m not sure what the best method would be.  I would appreciate it if anyone could help me figure out the solution to these problems.  

      Screen_Shot_2012-02-03_at_9.54.26_AM.png

        • 1. Re: What is similar to a union query in MSAccess?
          philmodjunk

          FileMaker cannot do a Union Query like you can in SQL based systems. Thus, you have to devise alternative methods.

          I would suggest that your best approach is to use one table for a catalog of all room assets available for selection and one table for listing all assets specified for a given room. This catalog table would include "typical" assets groups by self joining a "typical" record to a list of individual records in the catalog via a join table that lists the individual items. This is the same as having "Kit" entries in a products database that consist of individual items that can be selected individually as well as from a kit. Typically, a script is used so that when you select a kit item, it looks up the list of individual items and inserts them as additional individual items in your room assets table to list them as individual items.

          The relationship structure for typical asset groups in your Catalog would look like this:

          AssetCatalog-----<TypicalAssetsList>----AssetCatalog 2   (AssetCatalog 2 is a second occurrence of AssetCatalog)

          AssetCatalog::__pk_AssetID = TypicalAssetsList::_fk_AssetID

          A portal to TypicalAssetsList on your Assetcatalog layout can include fields from TypicalAssetsList and AssetCatalog 2 to list all items that make up a Typical Asset Group. The portal would be empty for individual assets that are not such a grouping.

          This approach avoids the Union Query issue altogether.

          If you want to go forward with separate tables, then getting a Union of them requires copying the data from or importing records into an additional table so that you can import or copy from each table in turn to combine the records. This produces your union but also denormalizes your data and can result in significant delays in many cases since a typical way to do this is to purge and rebuild this "union" table each time you use it to produce your list in order to make sure all changes to the assets selected are accounted for.

          • 2. Re: What is similar to a union query in MSAccess?
            bumper

            I cannot say if this approach will answer your question, but FMP has a built in SQL engine that is accessible through a variety of plug-ins. A good place to start an investigation would be Kevin Frank's www.filemakerhacks.com.

            • 3. Re: What is similar to a union query in MSAccess?
              ssp.columbus

              Thanks.  I have the tables and portals set up and am able to select the assets that make up the typical.  But I need help with the script that will add the assets from the "kit of parts" into the RoomDetail table.  I'm guessing I need to set some variables and use a loop to set fields, but I'm not sure how to actually write the "script that looks up the list of individual items and inserts them as additional individual items in the room assets table to list them as individual items."

               

               

              • 4. Re: What is similar to a union query in MSAccess?
                philmodjunk

                One thing you'll need to decide is whether the script will keep the entry specifying the "typical" group or remove it when adding the list of individual items. You'll also need to determine if you want to allow the user to specify a quantity for a "typical group".

                Rooms----<NewAssets>---AssetCatalog-----<TypicalAssetsList>----AssetCatalog 2

                An OnObjectSave Trigger on the NewAssets::_fk_AssetCatalogID field can perform this script:

                #Check to see if it is a typical group
                If [TypicalAssetsList::_fk_AssetID // I'm assuming this field is of type number ]
                   Freeze Window
                   Set Variable [$RoomID ; value: Rooms::__pk_RoomID ]
                   Go To Related Record [Show only related records; From table: TypicalAssetsList; Using layout: "TypicalAssetsList" (TypicalAssetsList)]
                   Go to Record/request/page [first]
                   Loop
                       Set Variable [$AssetID ; value: TypicalAssetsList::_fk_TypAssetID]
                       Go to Layout [NewAssets ("NewAssets")]
                       New Record/Request
                       Set Field [NewAssets::_fk_RoomID ; $RoomID ]
                       Set Field [NewAssets::_fk_AssetID ; $AssetID ]
                       Go to layout ["TypicalAssetsList" (TypicalAssetsList)]
                       go to record/request/page [next ; exit after last]
                   End Loop
                   Go to layout [original layout]
                End If

                This leaves the TypicalAsset entry in NewAssets intact and produce the listed items for a quantity of 1 such asset group. It can be modified to remove the original entry and compute quantities for each added asset if needed.


                • 5. Re: What is similar to a union query in MSAccess?
                  ssp.columbus

                  I would like the Typical Group to remain in the "NewAssets" table along with the assets from the TypicalAssetsList.  

                  I do need to have a quantity of "typicals" and I need the asset qty from the TypicalAssetsList to be multiplied by the qty of typicals iassigned to a room.  How do I modify the above script?

                  • 6. Re: What is similar to a union query in MSAccess?
                    philmodjunk

                    Add a set variable step to capture the Qty specified for the initial entry:

                    Set variable [$Qty ; value: NewAssets::Qty ]

                    put it right after Freeze Window.

                    Then, capture the Qty specified in the list of assets for the typicalAsset record:

                    Set variable [$ItemQty ; TypicalAssetsList::Qty ]

                    Put it at the beginning of the loop before you change layouts.

                    Then assign a quantity in the new record the script creates for the item:

                    Set field [NewAssets::Qty $Qty * $ItemQty ]

                    and put it with the other set fields.

                    This way you can specify a typical Asset group of "round table w/4 chairs" Qty = 2

                    and get 2 Table records and 8 chair records added to NewAssets.

                    • 7. Re: What is similar to a union query in MSAccess?
                      ssp.columbus

                      Thank you.

                      Per the last comment.  I do not need multiple records (2 Table records and 8 chair records added to NewAssets.)  I would like 1 record with a qty of 2 for the table and 1 record with a qty of 8.  Do I just leave out the Set field [NewAssets::Qty $Qty * $ItemQty ]?

                      Per the script - I can't seem to get it to work.  I have it written just as you described above but I'm not getting any additional records in the NewAssets (I'm calling it RoomDetail) table.  I only see the typical listed as an asset but not the assets that make up the typical.  I took a screen shot of the script and have attached it.  I'm not sure what I am missing.  The OnObjectSave Trigger is set on the RoomDetail::_fk_AssetID field which is a drop down to select the "assets"in the rooms_RoomDetail Portal.

                      Thank you for all the help!

                      • 8. Re: What is similar to a union query in MSAccess?
                        philmodjunk

                        I do not need multiple records (2 Table records and 8 chair records added to NewAssets.)

                        Correct, the script should create 2 records, on with "tables" and a Qty of 2 and one with "chairs" and a Qty of 8.

                        I'd first disable or remove the set error capture step. This step could conceal error messages that might provide a clue as to why the script is failing. (I almost never use set error capture except in scripted finds for this reason.)

                        next, make sure that TypASSETList::_kf_TypAssetID is of type number and check to see if it is evaluating correctly as nothing will happen if it evaluates as false. You can step through this script with the debugger enabled if you have FileMaker Advanced. If you do not, you can temporarily add a show custom dialog step jsut after the IF step and then see if it pops up when you select an Asset that has a typical Asset group assigned to it.

                        • 9. Re: What is similar to a union query in MSAccess?
                          ssp.columbus

                          The _fk_AssetID and _kp_AssetID are text fields.  Typically my key fields have a few letters than numbers for easy identification.  Is this a problem?

                          • 10. Re: What is similar to a union query in MSAccess?
                            philmodjunk

                            It may effect how the expression evaluates.

                            Not IsEmpty ( TypASSETList::_kf_TypAssetID )

                            should work for text fields.

                            • 11. Re: What is similar to a union query in MSAccess?
                              ssp.columbus

                              I just can't seem to get this to work.  I think I have the script just as you have described.  I have script debugger, but it just jumps from the If to the End If and nothing else happens.  I added the custom dialog and it does not appear so I guess I need to assume that the If statement is evaluating as false.

                              I did notice on your earlier comment with the original script the If statement is

                              If [TypicalAssetsList::_fk_AssetID ] but in the last comment with the Not IsEmpty you use

                              Not IsEmpty ( TypASSETList::_kf_TypAssetID ) I have tried it both ways and neither seems to work.

                              should this be _kfAssetID or _kf_TypAssetID?  The _kfAssetID is the "Typical"ID wheras the TypAssetID is the assets within the typical

                              This is what I have currently.  What else should I try?  I have records...they are not empty.

                              #Check to see if it is a typical group
                              If [Not IsEmpty ( TypASSETList::_kf_TypAssetID )]  
                                Custom Dialog

                                 Freeze Window
                                Set variable [$Qty ; value: NewAssets::Qty ] (added)

                                 Set Variable [$RoomID ; value: Rooms::__pk_RoomID ]
                                 Go To Related Record [Show only related records; From table: TypicalAssetsList; Using layout: "TypicalAssetsList" (TypicalAssetsList)]
                                 Go to Record/request/page [first]
                                 Loop
                                     Set Variable [$AssetID ; value: TypicalAssetsList::_fk_TypAssetID]
                                    Set variable [$ItemQty ; TypicalAssetsList::Qty ] (added)

                                     Go to Layout [NewAssets ("NewAssets")]
                                     New Record/Request
                                     Set Field [NewAssets::_fk_RoomID ; $RoomID ]
                                     Set Field [NewAssets::_fk_AssetID ; $AssetID ]

                                     Set field [NewAssets::Qty $Qty * $ItemQty ] (added)
                                     Go to layout ["TypicalAssetsList" (TypicalAssetsList)]
                                     go to record/request/page [next ; exit after last]
                                 End Loop
                                 Go to layout [original layout]
                              End If

                               

                              Thanks again.

                              • 12. Re: What is similar to a union query in MSAccess?
                                philmodjunk

                                I just can't seem to get this to work.  I think I have the script just as you have described.  I have script debugger, but it just jumps from the If to the End If and nothing else happens.

                                Since you have advanced, enable the data viewer (click =x) and check the value of TypASSETList::_kf_TypAssetID when you reach that If step.

                                For some reason, it is evaluating as false, which indicates that there are no related records in the TypicalAssetsList at the time this step executes. You may need to check to confirm that these records are correctly linked with the correct matching values in the key fields. One way to check this is to go to a layout based on AssetCatalog and give it a portal to TypicalAssets. Then find the record for the typical asset group you are trying to add to your NewAssets table and make sure that you see the expected list of records in this portal.

                                • 13. Re: What is similar to a union query in MSAccess?
                                  ssp.columbus

                                  Thanks.  I've put this issue on hold for the moment.  I thought I'd ask another question here so I don't have to re-explain in a new post.

                                   

                                  #1 I need to create a filtered Value List.  I've been reading the posts and can't find one that is similar to what I'm doing.  My catalog now has New assets, existing assets, and typicals.  On the catalog layout I have a portal to add the assets to the typicalAssetList.  I do not want to see the typicals in the value list and accidentally select a typical to go in a typical.  I only want to see the New assets. I have a field called AssetType that is either New, Existing or Typical.  How do I filter the Value List to only include the New Assets?

                                  #2  When I am assigning Assets to a room I would like a dwindling value list so I can not put an asset into a room more than once.  Again I've read a lot of the forum posts but they don't make it clear to me how to do this and they all seem to be FM v8.5 or earlier.  I tried a few of the methods and they have not worked.  Any suggestions?

                                  • 14. Re: What is similar to a union query in MSAccess?
                                    philmodjunk

                                    With regards to the "issue on hold" you may want to upload a screen shot of Manage | database | relationships.

                                    There are two options for #1.

                                    You can set up a calculation field as the source for your value list or you can set up a relationship and use it to filter the values in your list.

                                    If ( AssetType = "New" ; __kp_AssetID )

                                    This field will be blank for all records in Asset Catalog except for New assets and then you can set up your value list to list values from this calculation field instead of __kp_AssetID.

                                    #2 Dwindling value lists can be a challenge and it's one I've played with only a little. I have a demo file created in FileMaker 11 that uses a dwindling value list in a many to many relationship much like selecting your items for a room: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

                                    The key is a field that produces a return separated list of all ID's selected in the portal--which is then used in the relationship with a "not equals" operator to set up a conditional value list only for items not previously selected in the portal. The file also demonstrates 2 other methods for keeping the user from selecting the same record twice.

                                    The links I recommend for conditional value lists:

                                    Forum Tutorial: Custom Value List?

                                    Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                                    Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

                                    1 2 Previous Next