1 2 Previous Next 15 Replies Latest reply on Nov 2, 2011 4:04 PM by philmodjunk

    Many-to-Many Portal

    crtopher

      Title

      Many-to-Many Portal

      Post

      I have this relationship structure. I have a portal on the "case" layout showing "case_FFSItems". I can create records in this portal, adding records from FFSItems via the FFSItemsID field. I need to get the record in "FFSItems" that has the same scheduleID as the record in "case". Also, if I change the scheduleID in case, I need the FFSItems record to change to match (ie after I've already entered a new record in case_FFSItems with one scheduleID on "case" selected, I might need to change the scheduleID in that same case record and have the relevant FFSItems record accessed. 

      I know I'm missing a vital link here. Help!

      (I'll post an answer to this that has a snapshot of what the data in FFSItems looks like (showing that each Item can have a different associated scheduleID)

      Thanks

      Picture_1.png

        • 1. Re: Many-to-Many Portal
          crtopher

          Here is FFSItems table date (example)

          • 2. Re: Many-to-Many Portal
            philmodjunk

            I need to get the record in "FFSItems" that has the same scheduleID as the record in "case".

            What do you mean by "get the record"?

            Can two different case records have the same scheduleID?

            Can there be more than one FFSITems records that have the same scheduleID?

            • 3. Re: Many-to-Many Portal
              crtopher

              Hi Phil. Thanks again.


              Can two different case records have the same scheduleID?

              Yes. The scheduleID in "case" is a foreign key connected to the primary key in "schedule" occurence (as opposed to "scehdule 2" occurence snapped here - both based on the same "schedule" table of course)

               

              Can there be more than one FFSITems records that have the same scheduleID?

              Yes. As per the FFSI table data example above.

               

              What do you mean by "get the record" (in "FFSItems" that has the same scheduleID as the record in "case")?

              The important thing i'm trying to relate to the case is the itemnumber (FFSItems), and specifically the itemnumber with the same scheduleID as that in the related case record. (I see now that I need a relationship between "itemnumber" in the case_FFSItems and FFSItems table occurences). I will select the itemnumber I want in a portal to the case_FFSI table located on a "case" layout. Typically I will have selected a scheduleID on the case layout prior to going and selecting multiple itemnumbers in the portal, and I need each itemnumber to match that scheduleID as selected on "case". Then I need to tally the associated itemprices and take that total onto the case layout for use in calculations. I also may need to go back and change the scheduleID on the case layout aftre the itemnumbers are selected and have that change reflected in the case_FFSI portal (ie the itemnumbers don't change but the related schedule does)

              Thanks.

              • 4. Re: Many-to-Many Portal
                philmodjunk

                Schedule----<case-----<Case_FFSItems>------FFSItems>----Schedule 2

                I don't think you need a ScheduleID field in FFSITems. If I understand this correctly. All records in the join table that have a common CaseID also have a common ScheduleID. You can't, in fact assign your FFSItems records to a ScheduleID because of the many to many relationship here.

                Case 23 may link via that join table to FFSITems #5 and

                Case 4 may link via the join table to FFSITems #5

                Same item, but linked to a different case and through it, to a different Schedule Record.

                It will depend on the layout used, but you may need to set up this set of relationships here:

                Schedule----<case-----<Case_FFSItems>------FFSItems
                                                             v
                                                             |
                                                          case 2>---Schedule 2

                That way, a given record in the join table can link to a specific schedule record even from a portal to Case_FFSItems placed on a layout based on Case. And you don't need to define a field for ScheduleID in the FFSITems table nor will you have any update issues.

                • 5. Re: Many-to-Many Portal
                  crtopher

                  Thanks Phil.

                  I don't think you need a ScheduleID field in FFSITems. -

                  Yes I do because I need to pre-designate different prices to the same itemnumber according to different schedules. There are about 8 schedules. In the shot of the FFSItems table I included only 3 exampe schedules (eg MBS, DVA, AMA), but each of these schedules assigns a different price to the same item number, so the same itemnumber is repeated in different records but paired to different schedules. So each of these schedule_itemnumber pairings is unique. So i have the schedule ID field pre-assigned to the item number, not assigned on the go by the case table. I just need the schedule i assign to a case to link me to the correct itemnumber_schedule pairing, where I pick the itemnumber for the case, and update accordingly.

                  I have managed to do this successfully where there is only one item number related to the case (in the shot below, this is the area to the left of the case table with the ConsultItems table occurrences - ignore the case_otherRVitems occurrence). I need to do the same thing on the right but have a many-to-many relationship between the item and the case. Aaaaargh! I'm sorry if I'm not explaining myself well. 

                  • 6. Re: Many-to-Many Portal
                    crtopher

                    Hi Phil

                    Thanks for all your help on this - I'm sure I haven't been able to explain very well want I needed to do. Anyway I've solved the problem satisfactorily by using a script to loop through the portal rows to update the scheduleID field everytime I modify the schedule field on the main layout. It seems to work after ironing out a few glitches and working out how to automate all the updates and field and window refreshing! 

                    I think you guys call that a triumph of brute force over elegance - anyway thanks again for all your help with this.

                    Cheers, Chris

                    • 7. Re: Many-to-Many Portal
                      philmodjunk

                      Exactly what do you update and what kind of changes to the Schedule table are you making?

                      How do you know which FFSItems record to link to a given Case record in your join table? Are the FFSItemNumber's unique for every record?

                      I just don't see why you would need to update anything in the Join or FFsItems tables due to a change in a Schedule record...

                      • 8. Re: Many-to-Many Portal
                        crtopher

                        Hi Phil

                        ...what kind of changes to the Schedule table are you making? 

                        No I don't need to change anything in the schedule table. I sometimes have to change what schedule table record I have linked to in "case" (ie change the foreign key case::scheduleID) after I have entered other data into other fields (and indeed sometimes after I have left the record entirely).


                        How do you know which FFSItems record to link to a given Case record in your join table?  - I need to select the FFSI record based on its itemnumber;


                        Are the FFSItemNumber's unique for every record? No. The FFSItems::ID is the unique key. There are, at present, 8 repetitions of each itemnumber in the FFSItems table, one for each scheduleID. There are about 20 different itemnumbers. What is unique is the scheduleID-itemnumber combination. When I select an itemnumber, i want to see the itemprice from the matching schedule (1 of 8 possible), based on the schedule selection I make in "case". 

                        Thanks Phil.

                        • 9. Re: Many-to-Many Portal
                          philmodjunk

                          Ok, then the issue, as I see it is that the link between Case and FFsItems cannot solely be the FFSItemID number as this matches to more than one record in FFSItems and you need to specify the one for the current schedule selected for that case record.

                          Example: You Select FFSItemID = 5. This matches to FFSItem record with ID = 5 and Schedule ID = 6, but this is not the correct record, just the first such related record with that Item ID number. You really needed to select the record with ItemID = 5 and Schedule ID = 8, but have not actually done so here...

                          Looks like you need this relationship:

                          Case_FFSItems::ScheduleID = FFSItems::ScheduleID AND
                          Case_FFSItems::FFSItemID = FFSITems::FFSItemID

                          This requires entering a ScheduleID value into a field in the join table. You may be able to auto-enter this value or you can use a script to synch the specified value in Case to the related join records, or possibly by using an Unstored calculation field that returns the Schedule ID from the related Case record.

                          Personally, I'd avoid all that by giving each record in FSSItems a new unique value serial number with the current ID or a new "Type" field used to identify the set of 8 items with different schedule IDS, but same FFSItemID value. Then a conditional value list can only list the Items (using this new ID value that's unique for each record) with the same Schedule ID as that of the current Case record.

                          • 10. Re: Many-to-Many Portal
                            crtopher

                            Hi Phil

                            EVerything you've said excluding the last paragraph is exactly how i have set it up, and it's quite functional, but I have a nagging suspicion it can be done better. I would rather set it up like you mention in the final paragraph with a unique field for the FFSI items (in fact that is how I originally set it up) - but I can't work out how to relate this given my requirement for needing to match two fields to the join table (ie scheduleID and itemnumber). Does that fact that I need to be able to separately relate the itemnumber AND scheduleID mean that basing the relationship on a unique field on FFSI won't work?

                            You wrote: "...or a new "Type" field used to identify the set of 8 items with different schedule IDS, but same FFSItemID value"

                            I don't quite understand what you mean here - the scheduleID really is a 'type' field and this is already set up in the FFSI table; each set of 8 items has its type set by having an associated scheduleID (one of the 8 that there are). This is how I have already set it up. The problem is I need to relate an item by both its itemnumber and its schedule to the join table, but have the ability to change the related record if the scheduleID changes. The problem is that the scheduleID change is made in the "case" layout and I can't work out how to directly relate that scheduleID change, made in the case layout, to the FFSItems table - instead I have to get the scheduleID to be updated in the join table (via script at present) and relate that to the FFSI table. 

                            Thanks Phil. I'm admiring your persistence in dealing with my inability to understand this problem!

                            • 11. Re: Many-to-Many Portal
                              philmodjunk

                              The reason I would use a unique ID in place of FFSItemID is to avoid the need to match by two fields. With a unique ID, I only need to match by one field and this simplifies my relationship structure. I can always link back to the parent case record to determine schedule should I need to in a report based on the join table.

                              I don't quite understand what you mean her...

                              We're just tripping over a few word meanings here. Wink

                              Currently you have 8 records with the same FFSItemID, one for each schedule option. Thus these are eight records for the same type of item, but for a different price/schedule. If the current FFSItemID is sufficiently meaningful that users can recognize them in a value list, you can use them as second field entries in the value list. If not, then some other field that has the same value for all 8 of these records such as a description or name field can be used to help the user identify the item they are selecting.

                              • 12. Re: Many-to-Many Portal
                                crtopher

                                Ok I agree we are tripping over our meanings here...

                                Firstly, and I didn't clarify this last post and should have - the FFSItem::ID is the unique ID for the FFSI table, and always has been. The "itemnumber" field is the "number" (it's actually a text field because I don't need to treat it as a number, and indeed, it can occasionally contain letters) that is repeated 8 times with different schedules. I will re-post the FFSI table example I originally posted but re-sorted (same data) to show this (below). This example only includes 3 of the 8 schedules but it serves its purpose. 

                                Secondly, the "itemnumber" is the "type" field I use in the value list in the join table and is sufficiently recognisable to be selected. 

                                you write... the reason I would use a unique ID in place of FFSItemID is to avoid the need to match by two fields. With a unique ID, I only need to match by one field and this simplifies my relationship structure...

                                Originally I did use the unique FFSItem::ID field in the relationship between the join table and FFSItems, but the problem is, if I change the scheduleID in the case layout, I wasn't related to the correct record anymore. I think we are getting to the crux of with the following:

                                I can always link back to the parent case record to determine schedule....

                                HOW? How do I do this? What relationship(s) do I create?

                                ...should I need to in a report based on the join table

                                I don't just need to determine the schedule "should I need a report", I need it in real time, everytime I change a case record's schedule (ie even after the record has been committed) because I need the correct schedule prices to be associated with that case's itemnumbers (as selected in the join table).

                                 

                                Cheers, Chris

                                • 13. Re: Many-to-Many Portal
                                  philmodjunk

                                  Ok, rethinking a bit here. I understood that the ID was unique in another table, but not unique in this FSSItems table, but didn't think through all the ramifications. I think we need to stay with a match by two fields, the schedule and the Item ID. That way the update should be automatic. I didn't think through the fact that changing a schedule for the case then requires updating all the selectiong for FSSITems to specify the version specific to the new schedule.

                                  • 14. Re: Many-to-Many Portal
                                    crtopher

                                    In thinking about how to describe things better, really I should have said that the FFSItems table has records that can be sorted by two "types" (2 different fields) - "itemnumber" and "schedule". Once both "types" have been selected, I end up with a unique record from the FFSItems table. I want to select the "itemnumber" type from the join table but the "schedule" type from the case or parent table. Probably if I'd said it this way all along it would have made more sense!

                                    1 2 Previous Next