1 2 Previous Next 17 Replies Latest reply on Dec 21, 2010 10:29 AM by philmodjunk

    Simple newbie question(s):

    TonySeveerini

      Title

      Simple newbie question(s):

      Post

      Hello!


      I am new to all this database thing, and need to create a database an inventory database basically (with filemaker). So I will be using this thread so you can school me on my questions...

      Anyway, first question is;

      How can I copy and paste on the table, like in an excel sheet, how can I select many boxes at a time in the table?

      For now I have a field that has first and last name in it, I created 2 calculation fields that seperate the names as I want 2 fields; 1st being first name; 2nd being last. Anyway it displays perfectly, I just need to copy the text from the calculated fields to 2 new text fields, so that I can then delete the calculated fields and the "all names" field.

      In access I could simply edit the tables in many ways like in excel(copy and paste rows at a time etc.), is this possible in filemaker?

      thanks.

        • 1. Re: Simple newbie question(s):
          philmodjunk

          FileMake does not allow you to copy and paste multiple records at one time. You can copy them, but you can't paste them and have them align and put data into matching columns in the target table. They all get pasted as a single object and your paste winds up in a single field.

          You can, however, change the field types from calculation to text and their most recently computed value is retained as data in the text field. You can also simply define the fields with auto-entered cacluation field options that pull the first and last names from your combined name field and that leaves them as text fields in the first place.

          There are also scripted approaches that can move blocks of data from one table to another--thus achieving the same result as your block copy and paste--though this takes some doing to set up.

          PS. if you were to explain the reason why you want to do this, someone might think of an alternate design approach that eliminate the need for it in the first place.

          • 2. Re: Simple newbie question(s):
            TonySeveerini

            Hey thanks for the quick answer! Changing the calculation field to text is exactly what I need....

            Like I said I am still a total newbie to this, and basically I just think for searching the first and last names may be easier to use than a combined field, though I suppose that it could be sorted at later stages. BUT the calculation doesn't always work perfectly as some names are just strange so I think I want the separated fields.

            Anyway, I am still a bit confused on how the whole process especially in when you should copy tables/parts of them in the relatonships menu, and how the form actually relates to the tables. Maybe someone around knows some good total newbie tutorials?

            Also it is kind of problematic if the FileMaker can't export to excel. I just finished getting all this data from a crappy accounting program into an excel sheet by creating painstakingly long macro (which took some time as there were thousands of fields). I could of course use Access 2003 that I also have, but that thing is ANCIENT Embarassed

            • 3. Re: Simple newbie question(s):
              philmodjunk

              Like I said I am still a total newbie to this, and basically I just think for searching the first and last names may be easier to use than a combined field, though I suppose that it could be sorted at later stages. BUT the calculation doesn't always work perfectly as some names are just strange so I think I want the separated fields.

              In that case, I'd keep the full name field so that I can edit it to fix what appears in the calculation fields, or I'd use an auto-entered by calculation text field for each name field, so that I can fix the text in the individual name fields as needed. I'd prefer the first option, but you should also be able to get the latter to work also.

              It'd be very nice to be able to select subsections of the relationship graph and either duplicate them or copy and paste them, but that's not an option. You can, however, make duplicates of existing table occurrences so that you can define different relationships that link the same tables or to avoid "circular" relationships in the graph.

              Here's a table occurrence tutorial you might find useful. If you're pretty relational database savvy, you might even "drill down" to the second tutorial referenced in this one as it takes a very detailed look at how Table Occurrences control how a great many parts of FileMaker.

              Tutorial: What are Table Occurrences?

              FileMaker can indeed export to Excel and import from it as well. This is one of the easiest ways to move data from Access into FileMaker since "analyze by Excel" is a built in tool in Access. Check out the options for Import Records and Export Records and you'll find that Excel is a supported file type. There can be an issue with rounding for floating point numbers where the numbers aren't quite the same in FileMaker as they were in excel, but this is generally easy to fix with a Replace Fields by Calculation operation that fixes them after import into FileMaker.

              For More Information see:    Import from Excel decimal errors

              This is one of many acknowledged bugs that can be found in the Known Bug List here in the Report an Issue section of the forum.

              It can also be downloaded as a database file from:   http://www.4shared.com/file/8orL8apk/FMP_Bugs.html

              • 4. Re: Simple newbie question(s):
                TonySeveerini

                Ok I need some help yet again...

                Basically in the inventory there are 2 kinds of parts, those that you can sell and those that are used to make something else... The 2nd of those also have price that you sell the part with.

                So... Should I have a "type" field that would only have values 0 or 1 (Stock or inventory item). When the field is one you can actually select that part to be sold, when it's 0 you can't. Also when the field would be 0 the "sell price" would have to gray out as there is no sales price for inventory parts.

                Then there is also suppliers field, problem being many of the parts can have many suppliers. I would like the supplier to the part (I have separate list with all the supplier info). Basically, is it doable to have the suppler field as calculation field that would multiply the supplers or should I just create, say 4 supplier fields and just have maximum of 4 suppliers? Should the suppler on own created items say your own companie's name?


                Also a other newb question. If I create a dropdown menu that is supposed to represent all the different company ID's from a table, can what is actually be displayed in the dropdown menu be named after somethine else in that record (ex. company_name).

                Ok thanks again =).

                • 5. Re: Simple newbie question(s):
                  philmodjunk

                  That's a good list of questions! Wink I'm going to give generalized answers, you may want to research each answer more fully by looking up key terms in FileMaker help, the Knowledge Base or a book on FileMaker.

                  So... Should I have a "type" field that would only have values 0 or 1 (Stock or inventory item). When the field is one you can actually select that part to be sold, when it's 0 you can't. Also when the field would be 0 the "sell price" would have to gray out as there is no sales price for inventory parts.

                  A field with such a value (other values, including text can also be used) can certainly be used to control this. If you have a drop down, a conditional value list can list only the items that may be sold if you find that a useful idea, let me know. You might also be able to identify your 'non sale' items by the fact that their sale price field is empty. (Free items, if any, may have a sale price of zero). You can use conditional formatting to change the fill color of a field to grey when the item is a non sale item.

                  Then there is also suppliers field, problem being many of the parts can have many suppliers...

                  This is a classic "many to many" relationship issue. Your best bet is to list all your suppliers in a supplier table and then use a "join table" to link a given supplier to a given part. Here's a possible set of tables you might have for this:

                  Invoices-----<Line_Items>----Parts----<Part_Suppliers>-----Suppliers    ( ---< means one to many )

                  "...can what is actually be displayed in the dropdown menu be named after somethine else in that record"?

                  You can define a value list to list company ID's from the Companies (Suppliers?) table in the left column and the company name field from the same table in the right hand column. This way, the user can use the name to select the correct company, but the value list inserts the ID number as needed for you ID number based relationships.

                  • 6. Re: Simple newbie question(s):
                    TonySeveerini

                    Thanks a ton that helps a lot!

                    Though I dropped a bit on the last part... Then I figured how dumb I am. Of course I just leave the ID field out of the layout and just use the name field/other needed fields in the layout. Sealed

                    Ok one more stupid question; How can I turn off the auto remover of fields? If I remove a field in a form view it will destroy the whole field from the table... I want it to just remove the visual field in the form.

                    • 7. Re: Simple newbie question(s):
                      philmodjunk

                      Ok one more stupid question; How can I turn off the auto remover of fields? If I remove a field in a form view it will destroy the whole field from the table... I want it to just remove the visual field in the form.

                      I'm afraid that is not the case. Deleting a field from the layout does not delete it from the table associated with it. What are you doing and what are you seeing that makes it look like this is the case?

                      • 8. Re: Simple newbie question(s):
                        TonySeveerini

                        Yes, actually that is right.

                        The field just disappeared from both the form view and the table view when I deleted the box in form view. i thought it was deleted. Now I hit modify and got it back to the table view again.

                        Perhaps there is an option to always keep the table view untouched? Anyway, all is well now =).

                        Eh, yet a other thing... Is there a way to use calculated fields in the forms view? IE. I want to have quantity X price as valuebox in the form view... but not too keen on putting it in the tables as less fields in the tables the better.

                        • 9. Re: Simple newbie question(s):
                          TonySeveerini

                          OK I ran yet into more trouble, darn it if only there was a big tutorial somewhere.

                          1) So I have a contact list/ customer relationship list where I want to create contacts from the customer list via a portal. Everything is fine, except the created names do not appear in the contacts list records.

                          2) I have supplier/part "many to many" relationship, I have a portal in both pages linking to parts/suppliers. However I don't want to be able to create new records through these portals, I want just to be able to use existing records. I have tried pretty much every combination of "Allow creation of new records"... The relationships are as follows   CustomerID --------<CustomerID/SupplierID>------SupplierID.  (Same problems as question one apply here BTW, when I did create new records they do not show up in the corresponding records table, yet they do show up in the middle table, linking to a non existent IDs).

                          3) I have this "many to many"   "Supply -----<Supply/locationID >----- Location"   relationship. I want to have portal that displays location and quantity of supply.  Should the "quantity" field be in the middle table? And how would I go about multiplying the quantity?

                          4) Is there a way to set up container field as "insert picture" field only? (when you double click on it, it should insert image, not try record audio).

                          huugeeee thanks again!

                          • 10. Re: Simple newbie question(s):
                            philmodjunk

                            1) So I have a contact list/ customer relationship list where I want to create contacts from the customer list via a portal. Everything is fine, except the created names do not appear in the contacts list records.

                            Not enough info here. Can you describe in more detail, perhaps with an example?

                            2) Here's a demo file for many to many relationships:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                            Check it over for details on how to setup the needed portals and value list formatted fields. The key thing is that you need set up portals to the join table not the Customer and Supplier tables. And you will be creating new records, but only in the join table.

                            3) Is Quantity the total quantity available? if so, it should be in supply. Is quantity the total item in supply stored at a given location? If so, define the field in the Join Table. Multiply the quantity by what? Guessing here, but if you need the total on hand from quantities recorded in the Join table, define a calculation field in Supply that uses the Sum function to compute the total of the Quantity field in the join table. (Look up aggregate functions in FileMaker help, these are very useful functions that you will find many uses for.)

                            4) Yes, but a single click is much simpler to set up as there's no native tool for detecing double clicks in FileMaker. Select the field while in layout mode and use Button Setup... to format it as a button that performs a script. In your script, you can specify the Insert Picture option to insert a picture. Alternatively, you can just right click the field and specify Insert Picture, but this then leaves it possible for the user to perform other types of insert operations as well.

                            Is there a way to use calculated fields in the forms view? IE. I want to have quantity X price as valuebox in the form view... but not too keen on putting it in the tables as less fields in the tables the better.

                            I understand your desire to keep the number of fields in your table to a minimum and that's a good idea, but in FileMaker you often have to define calculation fields where in other systems such as Access you can define an unbound text box or a SQL expression that computes a value to be used as a field for a given form.

                            Since FileMaker 11 allows you to put global variables inside of Merge Text blocks on a layout, it IS possible to set up either triggered scripts that calculate and update its value or you can create a conditional format expression that includes using the Let function to assign a calculated value to a global field. Many developers are starting to use this approach. Just be careful to evalute how fast the layout refreshes and document the use of such variables carefully. (This is a form of "side effect" type programming that I prefer to avoid, but I can understand and respect the reasons why you might use this approach to reduce the number of field definitions.)

                            • 11. Re: Simple newbie question(s):
                              TonySeveerini

                              Ok I got most of it working now. Though still some problems...


                              In one to many relationship, how can I delete a record via a  portal? I can get the fields to empty properly, but that won't delete  the whole record in the related table. Instead there are bunch of empty  records. Tried some scripts but didn't work. I basically want to delete any record that has X, Y and Z field empty in a table.

                              In the many to many relationship I want the portal to joint table's only permitted values to be the ones in the  related table. Also if you pick a name in the table it should auto fill  the rest of the record's info that is put into the portal. So when I  select/type a companie's name into supplier (joint portal) in inventory list, it auto fills the "Phone#" field according to the name selected.

                              As of now I can put anything in the portal, and it just creates links to record ID's in the joint table that do not even exist. Yes the portal is

                              • 12. Re: Simple newbie question(s):
                                TonySeveerini

                                Buuuuump...

                                And merry christmas!

                                • 13. Re: Simple newbie question(s):
                                  philmodjunk

                                  I don't participate in the Forum over the weekend, so any questions posted after 5 pm PST won't be seen by me until sometime Monday morning. Wink

                                  To delete records via the portal, you use the delete portal row action. In portal setup... you first must enable this option for the portal. Then you either click the background of the portal row to select it and choose delete record from the records menu. A less confusing to the user option is to put a button in the portal row that uses the delete portal row action to delete the record. If you want to examine a fully scripted approach that you can easily import into your file download a copy of my Known Bugs List database. The portals in this solution all use the same script to highlight the portal row, then pop up a dialog confirming that this is the row to delete and then deletes the row. The script itself does not include any references to layouts, tables or fields so it imports cleanly when you use the Import Scripts tool in Manage | Scripts... (The layout and table specific data is passed to the script as a script parameter.)

                                  I want the portal to joint table's only permitted values to be the ones in the related table

                                  Take a closer look at the demo file I gave you a link to. Each of the pop up menus draw values from one of the two "many" tables. This limits the values to only those from existing records. If you prefer a drop down list, you can add a field validation rule (See field options in Manage | Database | Fields ), that limits values to only the value list.

                                  Also if you pick a name in the table it should auto fill the rest of the record's info that is put into the portal.

                                  This requires a bit of mental shifting of the gears from working with SQL systems, but all you need to do is add the needed fields from the related Table Occurrence of either of the "many" tables to the Join table as needed. When you select a value in the join table's match field, the corresponding data from the other table automatically appears. This is one more example of how table occurrences are the glue that holds everything together in FileMaker systems.

                                  • 14. Re: Simple newbie question(s):
                                    TonySeveerini

                                    Hey no problem... I assume you are not going to work on X-mas either.

                                    Anyway, I have got pretty much everything to work now, problem is that the results are not consistent (I dunno, maybe the existent value list that I add from excel file screw some values up, as for somereason my portals can't see all the added values in some cases but dunno).

                                    However, here is something I just can not understand. I have a very simple Customer ---< customer contact prject here. For some reason I can not add values thrugh the portal to the Contacts table... Also the contact table's ID number does not auto generate (though it does say auto generate serial). Even more strangely I do have a project where everything works good, but I can not find any differences between the two. There must be some value I am not considering here or something. There might be something wrong with my add script as well, kind of irrelevant, but I do like to lock the portals and only use the add/delete buttons to interact with them.

                                    Here is the file:

                                    http://www.mediafire.com/?gsq9w4i5swkwt3t

                                    Also just a small thing, but I pretty much have to use pop up menu as dropdown box values can be edited and there can be no alternate names for them (though I do much rather use those - as huge list is not a problem to handle for em). There is just one slight problem in pop up menus which is that the value can be deleted if you hit delete key. Perhaps there is a script that disables the delete key or something. How do you guys deal with huge checkbox lists BTW?

                                    1 2 Previous Next