9 Replies Latest reply on May 25, 2011 9:23 AM by philmodjunk

    Please help...

    yslim45

      Title

      Please help...

      Post

      Hi Experts,

      This is Lim here. I have a problem with conceptualising an excel file given by my boss to sort of convert it into a filemaker database. His idea  was to create a small application so that we could monitor the cashflow of the company. a cash in and a cash out. My idea was to create a list view where data could be entered in the cash in area and then when it is cash out the user just needs to click on a button and that particular record is placed in the cashed out area.

      I am new to filemaker and would like some assistance on this. How do I come up with the best relationship diagram possible so that I can begin the process of building the database?

      Regards

      Lim

      ScreenHunter_01_Apr._27_10.47.gif

        • 1. Re: Please help...
          philmodjunk

          Can't tell the purpose of all these columns from your screen shot as you didn't include the screen headers.

          Thus I can't tell what "the user just needs to click on a button and that particular record is placed in the cashed out area" would do in this layout.

          • 2. Re: Please help...
            yslim45

            Hi

            I have edited the screenshot basically it is viewed based on a monthly basis.what I meant by the user just needs to click on a button and that particular record is placed in the cashed out area". is moving that particular record according to the company name eg.SAP Singapore. to a new layout called cashed out when this record is placed originally on cashed in.

            Are there any better ways to do this (i.e importing this data into filemaker and then come up with the ERD for this?)

            • 3. Re: Please help...
              yslim45

              And I am supposed to generate a report similar to the excel file as well....

              • 4. Re: Please help...
                philmodjunk

                Lot's of questions and potential issues here.

                First, Many people who are familiar with Excel or other spreadsheets and unfamiliar with FileMaker get frustrated with FileMaker. Filemaker is not a spreadsheet, it's a very different type of software. You'll find that some things that are simple and straight forward in a spreadsheet are anything but simple to do in FileMaker. On the other hand, FileMaker can do many things that are difficult to impossible to do in a spread sheet.

                Bottom line, don't try to box yourself in by trying to replicate a spread sheet in your FileMaker layout. Other approaches that make better use of Filemakers strengths can often get the job done with much less work on your part, they just don't look exactly like the spread sheet.

                Don't think in terms of "moving a record from one layout to another". That's not really what's happening and thinking that way can lead you into making mistakes in how you design your database. Instead, you can change a value in a field so that your "cash in" entry is now a "cash out" entry. By performing a find for "cash in" or "cash out" records you can pull up the appropriate records that you want for each layout and this can be automated with scripts so that the user simply clicks a button to see the layout and the correct records will be displayed automatically.

                Don't link company records by the company name. This seems like the right way to go at first, but it's a recipe for major trouble. Use a serial number field in your relationships instead. (Just like people names, company names aren't always unique, they change their names, and you can get into trouble when you mis-enter a company name and then try to correct the error after entering data in related tables that link by company name...)

                Columns of data like you have here are easy in Spread sheets but can cause problems in FileMaker depending one what each column of data represents and how you need to work with the data after it is entered. You may find it easier to work with a single column ( or pair of columns if you have a debit/credit pair of columns) on your layout instead of multiple columns. I'm not saying you have to do it this way, only that it is easier to set up--especiall for data entry. While you can use 12 fields for each month or a field with 12 repetitions, either approach makes generating many common summary reports of this data very difficult to do. You can also use portals filtered by month and year to get your month based columns of data, but this is a more "advanced" Filemaker technique than you may be prepared to take on until you get a more basic "one column" approach working for you.

                • 5. Re: Please help...
                  yslim45

                  Hi PhilModJunk.

                  Thank you for your detailed and concise  reply. Your suggestions are really helpful. I will come up with more questions when I have them in mind.

                  Regards

                  Lim

                  • 6. Re: Please help...
                    yslim45

                    Hi PhilModJunk.

                    I managed to get the fields for 12 months as the hwading by using relationships  (12 table occurances)but could not populate each of the records in the portals for each of the 12 months by comparing a field in another layout called date_post and filter the portal in that layout by the date_post field. What could be wrong with it? It would be great if u could provide me with your email so I can send my file to you to make it easier for the troubleshooting process.

                    Use this URL to  view the screenshots.

                    https://picasaweb.google.com/105518519749634360695/ScreenCaptures?authkey=Gv1sRgCJvois-9hImjtwE#

                    Your help is very much appreciated,

                    Regards

                    Lim

                    • 7. Re: Please help...
                      philmodjunk

                      You need portals.

                      I don't see any portals for your monthly columns of data in your screen shots. And you need only one table occurrence for all your portals if you are using FileMaker 11. The difference in each portal is a portal filter expression you enter in each portal's Portal Setup... This can even be an expression that refers to a field so that you can use a script to control which month/year is selected for each portal by entering different dates into the controlling field.

                      • 8. Re: Please help...
                        yslim45

                        Hi I sctually created one row portal for each of the different portals. I have created a global field from another form and decided to use  a scrpt to trigger that global field to do that via only one table occurence( drop down list) but what if I want to show all the data? How should I configure the list so that it shows all the data?

                        Regards

                        Lim

                        • 9. Re: Please help...
                          philmodjunk

                          I'm assuming that you have FileMaker 11.

                          There are a number of ways to add an entry for "all" to your drop down list of values, but since you are setting up a layout where your portal can only show one record at a time. I'm not sure what you want when you say that you want it to show "all the data".