11 Replies Latest reply on Feb 21, 2012 3:46 PM by philmodjunk

    Compare value list to found records?

      Title

      Compare value list to found records?

      Post

      I am trying to compare a found set of records (find was done on Quarter of a given year) and compare that to a list of people.  Essentially I want to check that every person on the list has a corresponding entry.  If they don't have any entry for that Quarter, create a new record.  I don't want duplicates, either; only one per person/qrtr/year.

       

      I have seen various posts about testing for record existence and cycling through value lists, but nothing that quite seemed to address all the specifics of this problem.  I may be thinking about the problem all wrong; that's fine, too.  :)

       

      I am aware of doing a loop of Finds, but that seems cumbersome (would have to do a new window or redo the original found set when done).  Is there a way to check if a record exists in a found set, based on the names in the value list, without going through a bunch of Enter Find Mode/Set Field/Perform Finds?

       

      Thanks,

      J

        • 1. Re: Compare value list to found records?
          philmodjunk

          Instead of a custom value list, create a table for your list of people. This table can then be the source of values for your value list, but you can also define a relationship between it and the table one which you are currently performing a find. With the correct relationship, a single pass through the records in this table can be used to create records in each case where there is no related records. If I knew what criteria you are using for your find (all records with a certain date or with dates in a certain range?) I could provide a more detailed description of this method. (The relationship must recreate the logic of your find criteria.)

          • 2. Re: Compare value list to found records?

            We have a USER table which has ROLES defined; in this case the value list is a set of Users who have the PROD role defined.  These are the same names who might be showing up as assigned to do certain tasks.  My particular situation is creating a list of these PROD-flagged users in a separate table to track their available hours, separated by year/quarter.  So User-A will have a Q1 entry and and Q2 entry, etc, in this table. 

             

            To simplifiy entry I wanted my 'new quarter' script (attached to a button) to auto-populate this table for each PROD person (setting their available hours to a default value, which can then be individually edited).  But I don't want to create duplicate entries if that person is already listed for that Quarter (global selectors for year/quarter).  This would allow the addition of new people to the list without overwriting/duplicating someone who might already exist. 

             

            -- J

            • 3. Re: Compare value list to found records?
              philmodjunk

              We have a USER table which has ROLES defined; in this case the value list is a set of Users who have the PROD role defined.

              I don't see a need for a separate table to track available hours. This should be something you can do from a table based on your existing User table. you can perofrm a find to limit the records shown on this layout to just those with the PROD role.

              How do you currently track available hours in the database? Do you use a related table? If so, what relationship do you have inplace to link that table to USER?

              • 4. Re: Compare value list to found records?

                Did you just suggest to NOT make a new table?  :)

                 

                Hours is an upper limit; it is used for future plannings.  Each person has two possible types of hours, and they can change from quarter to quarter.  Multiiple quarters are defined/viewed at the same time (planning future schedules).  We want to track the history, and doing it in the current table would require at least 8 fields for each person (types x quarter), and then multiple entries for each person.

                 

                - J

                • 5. Re: Compare value list to found records?
                  philmodjunk

                  I've suggested that you don't need a new table of USER records, but you do need a related table to track the hours.

                  And this shouldn't need added fields for types x quarter.

                  But I am not clear in how you track hours currently and how you would use that data to add a record where one is missing.

                  How do you currently record the data that documents a person's schedule?

                  • 6. Re: Compare value list to found records?

                    Ah, I thought you meant not to make a new table to track the hour records, that they should be tracked in the Users table.  Hence the 2*4 new fields in the Users table to track those hours.  But that wouldn't account for history tracking.  So I think we are on the same page, I just didn't express it well.

                    So yes, a new table for tracking the hours of each person.  This isn't currently tracked in our system, so this is all new.  The new table would essentially use personID, quarter, year as a primary key:  if a new person is added to the list of PROD flagged folks, and then you hit the button to run the script, it would parse the currently selected quarter/year (global selectors). 

                    My main question is, since I already have a list of the PROD flagged folks from the value list, and I can generate a list of folks in this Hours tracking table for that quarter/year, I want to walk through each list and compare them to see if there is a new person who doesn't have Hours being tracked, and then create a new record.  I was just curious if it was possible to do this without doing a bunch of sequential finds, using the value list entries as find criteria. 

                     

                    -- J

                    • 7. Re: Compare value list to found records?
                      philmodjunk

                      "The new table would essentially use personID, quarter, year as a primary key:"

                      I suggest using an auto-entered serial number as the primary key. It's safer and more reliable. The PersonID would be the foreign key linked to USERS and the quarter and year fields would serve as "filters" for matching to different subsets of the total set of records linked to a given USER record--but I think I'm just using more precise terminology here.

                      Let's say we have this relationship:

                      USERS::PersonID = Hours::PersonID AND
                      USERS::gQuarter = Hours::Quarter AND
                      USERS::gYear = Hours::Year.

                      Enable "Allow creation of records via this relationship" for Hours in this relationship.

                      Now your update script can look like this:

                      Go to Layout [Users]
                      Enter Find Mode []
                      Set Field [USERS::Type ; "PROD" ]
                      New Record/Request
                      SEt Field [Hours::PersonID ; "*" ]
                      Omit Record //this is the same as clicking the omit record in a manual find
                      Set Error Capture [on]
                      Perform Find []
                      Loop
                         Set Field [Hours::PersonID ; Users::PersonID]
                         Go to Record/Request/Page [Next ; exit after last ]
                      End Loop

                      TO use this script, you select or enter a quarter in the gQuarter field a Year in the gYear field and then you perform the script. Both gYear and gQuarter must be defined with global storage for this to work.

                      You could actually just perform a find for all PROD users records, loop through all of them and you'd get the same results. In cases where the related Hours record already exists, the script sets the Hours::PersonID field to the value it already stores so there is no actual change when there's already a record. The Omit request in the find just reduces the list of records so that the script will execute with fewer loops.

                      • 8. Re: Compare value list to found records?

                        Thanks Phil, this works quite well.  However, I had a few questions. (Always with the questions...)

                        Can this be made to work with the global fields in another table?  I would like to NOT clutter up the Users table further, if I can help it.  I can see that that is really how narrowing and equivalence of records is being done, and so that would have to be re-created in some other way.  I guess it may just come down to a choice between doing it all in the script with subsequent finds (which is what I was originally trying to avoid, I know), or doing it with this relationship.  :)

                        And maybe doing what you suggested, just searching on all PROD users and looping, is a nice middle ground.

                        I was surprised that the asterik in the find didn't also return blank records.  Is that just the way FM behaves?  I know I can find blanks using an equals sign, but the description of 'zero or more' implies blanks, also.

                         

                        Thanks,

                        - J

                        • 9. Re: Compare value list to found records?
                          philmodjunk

                          Global fields can be defined and used in any tabel EXCEPT when they are match fields in a relationship. Then they have to be defined in a specific table or there is no way to use them to create the desired relationship.

                          I was surprised that the asterik in the find...

                          It's always worked that way as far back as I can remember.

                          • 10. Re: Compare value list to found records?

                            Is it possible to avoid part of the script by basing the layout on a TO of Users, where Users::Role_Prod = 1 already? I was thinking about the original display upon going to that layout, and which users were initially displayed.  Then I wouldn't have to do a the initial find of Role = Prod users is my thought.

                             

                            But then I think that that wouldn't quite work (and hasn't in my testing so far) because that TO's restriction would only be based on the context of coming from Users.  But if you are just basing a layout on it in the first place, there is no step going from USERS to USERS_PROD, so nothing upon which to constrain the set.

                             

                            -- J

                            • 11. Re: Compare value list to found records?
                              philmodjunk

                              Layouts are based on table occurrences, not a relationship. The only way I can think of to do what you describe is to move that part of the script into a second script that is performed each time that field is changed and each time the file is opened. (Found sets on hosted files are not retained as each user may have a different found set.)

                              This does suggest a method I've been resisting and that's to create a separate table of just the PROD users. I'm not a fan of that idea due to the duplication of your data this requires and don't see where it would improve the situation much, but it would work.

                              Is there a reason why you want to do this? Big delays when you run this script? or ??