14 Replies Latest reply on Jun 24, 2013 12:26 PM by kraftfm

    Self Join issue with Unstored Fields?

    kraftfm

      Have read the discussion strings and tutorials on Self-Joins and can't find answer to my specific issue.

       

      Project - Equivalent to a typical Invoicing solution. Have an invoice for a specific customer with a portal for invoice line items (4 tables, customers, invoices, invoice line items and products all with standard primary and foreign keys set up). Products include a name and a size/type..

       

      User Request - When they key in a new invoice line item they want to see a field to tell them how many of that product + size/type combination have they ordered/bought (e.g. this would be different for each customer and each instance in the Invoice Line Item Table).

       

      Following the Self Join threads I was able to set up the new relationship and although I followed Answer ID: 3071 in Filemaker Support, the Self Join and result doesn't seem to work. I believe because some of the data is "unstored", specifically the Product Name, may be the issue.

       

      I apologize if this is simplistic. Just my first project with Filemaker Pro 12

        • 1. Re: Self Join issue with Unstored Fields?
          keywords

          When you set up the relationship you would have got a warning from FM if the relationship used unstored fields, to the effect that the join would not work. Try resetting the relationships to see if you get this warning. If you do, then you need to overcome the problem. It is possible to create a relationship that will go FROM an unstored field TO a stored on, but not the other way around. Ideally, all your relationships should use key fields created for the purpose, not fields like Product Name which can cause this very issue.

          • 2. Re: Self Join issue with Unstored Fields?

            In LineItems, you should have a ClientID and a ProductID.  Create self-join of LineItems and join as:

             

            LineItems::ClientID = LineItems 2::ClientID

            AND

            LineItems::ProductID = LineItems 2::ProductID

             

            Create a summary field in LineItems which counts any never-empty field ( usually the primary key ).  Now place the summary FROM THE JOIN into your LineItems portal.

            • 3. Re: Self Join issue with Unstored Fields?
              kraftfm

              Thanks for this response.  Some comments:

              • I do not have a ClientID in LineItems.  It joins the Invoice table and the Products table so the Foreign Key fields are only ProductID and InvoiceID (of course the primary is LineItemsID).
              • Using the InvoiceID instead of the ClientID with the rest of your example produced a result but only a count of the Line Items (each invoice had 1, 2, 3, 4... in this Count field depending on how many products there were in the invoice)
              • Again, what I want is how many "Bicycles which are Blue" (for instance) or "Bicycles which are Yellow", so when they order a second time it shows "2", a third time the record reads "3", etc

               

              I am SURE it is user error.  Again my apologies.  Clarification would be appreciated!!

              • 4. Re: Self Join issue with Unstored Fields?

                Do you track inventory?  Aren't blue bicycles different SKU than yellow bicycles?  Is the color a simple pop-up on LineItems row and the Product the bicycle? 

                 

                I would include ClientID in LineItems as auto-enter.  It seems inevitably that tracking items by Client arises.  There are ways of achieving your request including filtered portals but it would be best to use relationship to filter the count and/or another table. Why do you want this information?  And why is it important to list #2 and then #3 ... a running summary in the portal will count them (look at the checkbox on the summary below the fields list).  no that is not going to help ... where would these numbers appear?

                 

                If you can provide more information as to the purpose and where this information needs to be displayed, we can help you out.  

                • 5. Re: Self Join issue with Unstored Fields?
                  kraftfm

                  LaRetta - thank you very much.

                   

                  I tried to use a generic sample but alas I am not making sense so I will provide the real situation.

                   

                  The application is for an alternative medicine counselor.  She wants to be able to know, for a given 'Client', when she gives a medication in a specific dosage, how many times has she given that Client that specific medication + dosage combination (e.g. Ambien 5mg).  Medicine name and dosage are two different fields as you would expect.  I have a four table solution and have designed it like a classic invoice solution (therefore my analogy) as:

                   

                  Client = Customer

                  Consultation = Invoice

                  Medication = Invoice Line Item (you can get multiple medicines per consultation)

                  Pharmacy (medicine name and description table) = Products

                   

                  It is a standard configuration with Client linked to Consultation and Medication linked to both Consultation and Pharmacy so there are just one-to-many relationships.  The relationship graph is as follows:

                   

                  Screen Shot 2013-03-15 at 3.46.06 PM.png

                   

                  So to your question, each medicine (bicycle) has its own primary Pharmacy ID, but dosage is a separate field (there are 3500 medicines in the table) within the Medication table.

                   

                  As to portals, the user really works within two layouts, Client and Consultation, each with multiple tabs and views all of the information through portals.

                   

                  Does that explain things sufficiently??

                   

                  Thanks again!!!  Mike

                  • 6. Re: Self Join issue with Unstored Fields?

                    Hi Mike,

                     

                    There are various ways to resolve this and attached is my suggestion. This approach uses  a global in Medications to hold the ClientID and on both Clients and Consultations layouts use OnRecordLoad script trigger setting the Medications::gClientID  using simple GetField ( "ClientID" ) using a single script.  The process requires one additional table occurrence (your self-join), a ClientID in Medications (as I mentioned before) and the global ClientID.  Please see attached (you will need to sign in to download). 

                     

                    This means that the count of unique client/medication/dosage can reside right in the Medications portal next to the medication/dosage User is entering.  The portal displayed on Consultations was duplicated and then placed on Clients and please note that the yellow field is the summary field from the medications self-join to pull that count through to upline.  And the pink field is the global in Medications which is set via script trigger.

                     

                    ADDED:  please see revised file ModificationsMOD that I posted below

                    • 7. Re: Self Join issue with Unstored Fields?
                      Oliver_Reid

                      That reltionship should work fine. Count(Medication_2::pk) will provide the number you want, unless either of teh fks you show is not stored.

                       

                      Make sure the context of the calcuated field is "Medication"   if it is not, that might explain the issue.

                      • 8. Re: Self Join issue with Unstored Fields?
                        kraftfm

                        LaRetta

                         

                        Thank you so much.  It was amazing for you to do this.  Some questions as I have still some user error:

                        • Do all of the IDs have to be numbers?  I set up my primary and foreign keys as text.  Does that impact it?
                        • The summary field isn't separating out the medication + dosage for a client.  It is just giving me a number that is a total of all medications given for that client and 'that' consultation (e.g. if there are three medications it says "3" in each line item for the field sCountMed.  It isn't even totalling across all client's consultations.
                        • In the script that is OnRecordLoad, the calculation's result is GetField("ClientID").  It does not say GetField("Client::ClientID").  What am I missing here?  ClientID is the primary key for this table. 

                         

                        The script triggers have been put in, albiet from the above I may have done something wrong, and I did go in-out of the layouts.

                         

                        I am confused about what Oliver_Reid has stated below.  In your example I just get the answer in the sCountMed field in the Medication table.

                         

                        Appreciate it.  Know this is above and beyond.  I feel I am very close and quite excited to hopefully get this in.

                         

                        Regards,

                         

                        Mike

                        • 9. Re: Self Join issue with Unstored Fields?

                          Hi Mike,

                           

                          Do all of the IDs have to be numbers?  I set up my primary and foreign keys as text.  Does that impact it?

                           

                          Your unique IDs should be the same data-type whichever you choose.  For utility keys sometimes we mix text key (with multiline) to number field etc but primary and entity keys should be same.

                           

                          The summary field isn't separating out the medication + dosage for a client.  It is just giving me a number that is a total of all medications given for that client and 'that' consultation (e.g. if there are three medications it says "3" in each line item for the field sCountMed.  It isn't even totalling across all client's consultations.

                           

                          Is it not working in the file I provided?  If my file is not giving the correct result, please take one record and tell me specifically where it is off.  If only your file then I suspect it is a context issue ... the summary field is properly evaluating according to where you have placed it (according to its table occurrence perspective) so I will need to know the table occurrence name for the summary field which is displaying the incorrect result as well as the table occurrence name of the portal in which it resides.  We'll get you there yet ... it's a small thing, I think   unless my file is still missing the point in which you need to give me one example from MY file where it doesn't work.

                           

                          In the script that is OnRecordLoad, the calculation's result is GetField("ClientID").  It does not say GetField("Client::ClientID").  What am I missing here?  ClientID is the primary key for this table.

                           

                          Ah yes, well I never use the pk, fk stuff.  If a ClientID exists in a table that isn't called Clients then it clearly is the foreign key.  I also name my primary keys all just ID which allows me to relate and script using them easily.  But since the ClientID is the same in both my tables, I could do that.  You will need to have two separate scripts (one for OnRecordLoad in Clients and another one for Consultations.  But we don't yet know if we are on same wave-length so please set me straight so we can nail this puppy for you!

                           

                          ADDED:  My apology ... the post was scrambled and I had to reformat it.

                          • 10. Re: Self Join issue with Unstored Fields?
                            kraftfm

                            LaRetta,

                             

                            I cannot thank you enough for taking the time to solve this with me.  This again shows the strength of the Filemaker community and the depth of your skill set.  I finally went through and replicated what you had done.  I guess I should have paid more heed to what Oliver mentioned below because my issue was, at a minimum, that I didn't pick up the Count field from the correct table occurence.

                             

                            Again, thanks very much!!  Your assistance was above and beyond.

                             

                            Best,

                             

                            Mike

                            • 11. Re: Self Join issue with Unstored Fields?
                              kraftfm

                              LaRetta

                               

                              Just uncovered an issue with the solution you provided.  While the table does 'count' the number of instances of a 'Drug' and specific 'Dosage' combinations, it doesn't keep the Client counts unique.  Therefore, if you put in the same drug and dosage into two different clients you will get a count that sums both of the Clients together vs. keeping them separate.

                               

                              What is the fix for this?

                               

                              BTW - I have attached the solution you provided in case you didn't have it handy.

                               

                              Thank you VERY much.

                               

                              Mike

                              • 12. Re: Self Join issue with Unstored Fields?

                                Hi Mike,

                                 

                                Here is a revised file which removes the global and layout trigger completely.  Please note that, if you add a drug/dosage in the Medications portal, the current line will update with the overall total for the client (the yellow field) but that any prior entries of the same medication on the same Consultation will not update to the new total until you commit.

                                1 of 1 people found this helpful
                                • 13. Re: Self Join issue with Unstored Fields?
                                  kraftfm

                                  Thanks very much!!

                                  • 14. Re: Self Join issue with Unstored Fields?
                                    kraftfm

                                    LaRetta,

                                     

                                    You helped me with this issue on Self Join with Unstored Fields back in March.  The solution counts the drug/dosage instances for a specific Consultation.  You may want to look back at the solution you provided.  Here is my question:  The solution updates "all" of the Count fields.  Therefore, when you get to the fourth time a drug/dosage is given, for example, it puts a '4' in each record/line item.  That means the 1st, 2nd, 3rd and 4th instances of the drug/dosage has a '4' in it.  Is there a way to modify this so that the 1st occurrence has a '1', the 2nd a '2', the 3rd a '3', etc and those don't change?  Therefore the user can see when the first time a drug/dosage was given and on through that Client's history?

                                     

                                    Appreciate the assistnace.

                                     

                                    Mike