4 Replies Latest reply on Jul 22, 2010 2:11 PM by billg999

    Relationship hassles

    billg999

      Title

      Relationship hassles

      Post

      I have about 12 tables in my syste, most relationships work fine. However, one pair: "Production" and "Production summary" are related through a field "Date". What I want to achieve is that in the portal for each record in  "Production" with identical date and Docket no entries , it will show  several records from "Production summary" when date and docket no are  set the same as in "Production".

      I have a portal, which filters records by "Docket no" and "Date".The Production layout also has some of the fields of "Production Summary".

      What happens at present is that in consecutive records in "Production", if the portal filter parameters are the same, the "Production summary" fields are accessable, but new entries in the last  record equalise the entries in the previous recordand vice versa, if the filter parameters are different, the "Production summary" fields are not available.

      Is this a bug, or am I trying to do the impossible?

        • 1. Re: Relationship hassles
          LaRetta_1

          Hi Bill, sorry but I do not understand.  If you have a match between Production and Production Summary based upon two fields as:

          Production::Date = Production Summary::Date

          AND

          Production::Docket No = Production Summary::Docket NO

          ... then the portal will display results if there is a match and will not display results if there is no match.

          Bill said, "What happens at present is that in consecutive records in "Production", if the portal filter parameters are the same, the "Production summary" fields are accessable, but new entries in the last  record equalise the entries in the previous recordand vice versa, if the filter parameters are different, the "Production summary" fields are not available."

          What do you mean by the portion in blue?

          • 2. Re: Relationship hassles
            billg999

            I open a new record inProduction, then enter values in production summary, then when I check the details in the related fields in Production, they are always the same value. Thinking about it, I checked and found that production summary only has one record; this explains the bit in blue. Thing is,  I need to set a new record n production summary, I have a button on screen to do that, but am not sure about the script: ¨New Record¨, which would seem the obvious function to use, defaults to the Production table. I need to specify the table. but this function does not allow for parameters, so I cant specify the table.Any ideas?

            • 3. Re: Relationship hassles
              LaRetta_1

              All script steps fire on the table occurrence upon which the layout is tied.  If you fire a New Record script then it will create a new record in Production.  You need to go to your Production Summary table to add a new record there.  You would start your New Record script with Go To Layout [ layout based upon Production Summary].

              However, without seeing your file, I am only guessing if you should even be going this route.  I don't understand why you even need the Production Summary table at all nor why they are joined on Date and Docket No.  If you have a Production Summary table, it MIGHT make sense that it holds one of every Docket No/Date combination for summarizing but there are better ways of even handling that need.  For example, if the above is true AND if you have 'Allow Creation' on in your relationship graph between these tables then when you create a new record in Production and it inserts the Docket No and Date, you can fire a script (right from your Production layout) with a simple:  Set Field [ Production Summary::Summary Date ; Production::Date ].  This will create a new record in Production ONLY IF there is not a matching summary record with that Docket No and that specific date.  If a record with that date already exists in Production Summary, nothing at all will happen (you will not get duplicate record in Production Summary).

              But again, your structure and purpose of Production Summary table is only speculation.  Is this a solution you inherited?

              • 4. Re: Relationship hassles
                billg999

                Thanks for that, No it is a new design, I''m failry new to FM, having done most of my previous database work in SQL. What I need: My customer has a plant that assembles fruit packing trays from basic blanks. I have a production table/layout that lists details of today''s production scheme in terms of type, customer etc. Now, for each pallet he needs to list things like rejects (5 categories) and repairs. So, I set up a table for this, production summary, linked to Production with the date and docket number fields as a unique identifier. Now, for each pallet for the day''s production we need to enter reject/repair details, ie for each record in the production table ther will be several records in the prod sumary table. I want to enter these details from the production layout with a "New Pallet" button, whose script needs to set up and fill a new record in production summary. It would be so easy in SQL: Insert <fields> into <table> where <Table::date == etc. Any suggestions? Thanks.