9 Replies Latest reply on Sep 3, 2014 10:22 AM by philmodjunk

    Creating a table using limited fields from another table

    RogerBirch

      Title

      Creating a table using limited fields from another table

      Post

           Having tried to get my head around a solution, I am getting more and more confused as to the best way to create the answer I am trying to perform.

           I am not too sure whether it is a relationship situation or a matter of just creating a table displaying fields from another table using scripts.


           I have created a contacts table with many contacts plus including contacts enquiring and booking accommodation in our apartments.

           I want to create a separate Table, called "InvoiceJournal" showing ONLY clients who have been allocated (by me manually) an Invoice Number in my "Contacts"table after they confirm a booking.


           In a nutshell, my Contacts table includes all enquires from prospective clients plus many other  contacts I have but I wish to have a separate table showing only those who have booked with me and I have given them an Invoice Number.


           I am obviously missing the point as I have tried many combinations without success.


           The closest I have done is to produce an additional layout base on the fields within the Contacts list, but then "sorting" becomes a problem as I want the Contacts to be sorted by FullName and I want the InvoiceJournal to be sorted by InvoiceNumbers!!

           Any help in this matter would be gratefully received.

           I look forward to any response. Thanks.

        • 1. Re: Creating a table using limited fields from another table
          philmodjunk

               Can a given Contact record have more than one Invoice Number allocated? If not, I see no need for an added table.

               Perform a find by putting an * in the Invoice Number field as your search criteria, then use sort records to sort them by invoice number if that is what you want for a sorted order.

               But if you might need to assign more than one invoice number to a given Contact, then you'd need that added table and would set up this relationship:

               Clients::__pkClientID = InvoiceJournal::_fkClientID

               A portal to InvoiceJournal can be put on a Clients layout to use for assigning those multiple invoice numbers.

               And an InvoiceJournal layout can then be used where you add any needed fields from Clients to that InvoiceJournal layout.

          • 2. Re: Creating a table using limited fields from another table
            RogerBirch

                 Thanks Phil for your prompt response.
                 Its always possible that we would have a repeat client so guess the second option you recomend would be the best.
                 I would be grateful if you could expand upon this a bit please.


                 i.e. in the contacts table, would i rename the ID field as _pkClientsID and if I wanted to show "Name", "Arrival date", "No of Nights", "Total Cost" etc, which are all fields on my "Contacts" Table, would I copy these across from one table to another or create new in the "Invoice"table which also would include _fkClientID as the relationship link.

            • 3. Re: Creating a table using limited fields from another table
              RickWhitelaw

                   Sorry for the short answer, but there's no point in having the same data in more than one table.

              • 4. Re: Creating a table using limited fields from another table
                philmodjunk

                     Since I did not know the name for your ID fields, I used my own names for them. You do not have to rename them. You can if you find doing so useful.

                     Once you have your relationship in place, you can:

                     Go to the InvoiceJournal layout.

                     Enter Layout mode.

                     Then either click the field picker (New in FileMaker 13) or drag and drop from the field tool in the Status area tool bar to open the specify fields dialog.

                     In either dialog, select your Contacts table occurrence in the drop down at the top and then use the list of fields to select a field for your layout. This adds a field from your contacts table occurrence to your InvoiceJournal layout. The relationship will enable FileMaker to display data, the "Name, "Arrival Date", etc... from the Contacts record that is linked to the current InvoiceJournal record.

                     You may want to use a layout based on InvoiceJournal to produce a Creating Filemaker Pro summary reports--Tutorial that includes data from contacts.

                • 5. Re: Creating a table using limited fields from another table
                  RogerBirch

                       hi Phil, many thanks for advice, at the moment i am still trying to get a head around my issue (amongst other things!).

                       On Friday there is a webinar on Relationships which i shall listen into and maybe this will give me some guidance.

                       I may need to seek some extra advice if you are willing please after next Friday.

                  • 6. Re: Creating a table using limited fields from another table
                    RogerBirch

                    Hi Phil, having watched the webinar last Friday, there is still fog!!!

                    If I may reiterate, In a nutshell, I have created a table called "Contacts" which contains all contact details of family, friends, useful contacts, and those of our clients using our self-catering apartments.

                    With reference to our clients, I also create an excel spread sheet which duplicates some of the information I have entered into my "Contact" table, i.e invoice number for each client, date of arrival, number of people, number of nights and cost etc.

                    Duplicating this information to this excel spread sheet is obviously a waste of my time and I feel that I could do it more effectively by creating another table, say called "Invoice list" and sorted by invoice number  which is linked to the "Contact" table but only showing the information as mentioned above and only showing those entries that have been given an invoice number. (by me)

                    I have created a layout based on this information but I have to change the "sort records order "  depending on which layout I am looking at and it also shows all the entries from my "Contact" table which is unnecessary, so this is why I feel that a linked table would be best.

                    It is at this point that I cannot get my head around the solution.

                    If you are able to suggest how I can do this based on the information above, I would be most grateful.

                    • 7. Re: Creating a table using limited fields from another table
                      philmodjunk

                      You appear to be putting data all in one record that should be divided into individual records--likely in more than one table in some cases all linked in relationships.

                      • 8. Re: Creating a table using limited fields from another table
                        RogerBirch

                        I see what you are saying but if I create a table just for "clients", some of the entries/records are just enquiries from clients, some confirmed bookings and some cancellations.

                        After a client has made an enquiry, this may be later turned into a confirmed booking at which stage I give it an invoice number.

                        It is the "confirmed bookings" that I want to siphon out into a separate list/report table all sorted by the "invoice number". The rest of the data in this table would be the name, arrival date, no. of persons, no. of nights etc.

                        Hoping this makes for more clarity?

                        I thought I might send you an image of my typical contacts table/layout if it helps.

                        • 9. Re: Creating a table using limited fields from another table
                          philmodjunk

                          It is the "confirmed bookings" that I want to siphon out into a separate list/report table all sorted by the "invoice number".

                          You can manually or via script perform a find for records that are "confirmed bookings"--either all such records or all in a specified range of dates.

                          Once you have performed the find, you can use Sort Records to sort them by Invoice Number.

                          Here's a thread with quite a few scripted find examples: Scripted Find Examples

                          Caulkins Consulting, Home of Adventures In FileMaking