Value List items in a table

[JoeHardstaff]

Hi There,

I have a script that populates a table with StudentID based on a value list I created. This works fine when creating new records. Such as for a new weeks register. I click a button and it populates the table with a new week and all the pupils are added. 

Now I want the same script to function in a different table but I don't want it to add all the pupils every time. I only want it to add new pupils. So I need a step in the script that checks the table first to find records that already exist and then add only the records from the value list that are not in the table.

A picture of my script is attached

Thank you for any help. 

[Aurigo]

Hi Joe,

Strategy: you first want to reduce the list then run your script.

Create two ValueLists - one of the existing Students, one of the one's you want to add. Let's call them listA and listB.

Create a custom Function (Assume you are using FileMaker Pro Advanced):
 

Quote

 

NotList (listA ; listB ) 

// returns List with Items in A not in B
If (listA <>"";
     If(PatternCount("¶"&listB&"¶"; "¶"&LeftValues(listA;1)); "";LeftValues(listA;1))
 &    NotList(MiddleValues(listA;2;ValueCount(listA)-1); listB)
)

 

Now pass the two lists from a script or Formula field to the Custom function to get the reduced list. 

Now you can run your script.

lists.fmp12

Edited February 18, 2016 by Aurigo

[comment]

If you set the StudentIDfk field in the target table to validate as Unique, Validate Always, you could then simply import the records from the Students table. This would skip all existing values and create new records only for the remaining ones. A similar result can be achieved by selecting "Update matching records in the file" + " Add remaining data as new records" for the import.

 

4 hours ago, Aurigo said:

Strategy: you first want to reduce the list then run your script.

For a task that requires a script to run anyway, I would prefer to put all the required logic into the script and not branch some parts out to a custom function.

[JoeHardstaff]

Hi

Thanks for both of your comments, I REALLY appreciate it. 

I have tried Aurigo's way and i can get the lists to work from the existing tables and create a new list. This bit works fine but when i run my scrip based on the new created list that only has the values of StudentID's that are not already there, it adds one blank row only. 

Comment, I like your method but can this be automated by a script when the layout is entered?

 

Thanks again.

Edited February 19, 2016 by JoeHardstaff

[comment]

10 minutes ago, JoeHardstaff said:

can this be automated by a script when the layout is entered?

Yes, of course it can be automated by a script (though I am not sure you would want to run the script every time a layout is entered). See attached demo.

ImportNew.fp7

[comment]

For completion, here's how a script comparing two value lists in a loop would look;

Go to Layout [ “Target” (Target) ] 
Set Variable [ $sourceIDs; Value:ValueListItems ( Get (FileName) ; "SourceIDs" ) ] 
Set Variable [ $targetIDs; Value:ValueListItems ( Get (FileName) ; "TargetIDs" ) ] 
Set Variable [ $n; Value:ValueCount ( $sourceIDs ) ] 
Loop 
  Set Variable [ $i; Value:$i + 1 ] 
  Exit Loop If [ $i > $n ] 
  Set Variable [ $id; Value:GetValue ( $sourceIds ; $i ) ] 
  If [ IsEmpty ( FilterValues ( $id ; $targetIds ) ) ] 
    New Record/Request 
    Set Field [ Target::SourceID; $id ] 
    Commit Records/Requests 
  End If 
End Loop 

---
BTW, you didn't say why you need this. It looks like what you are accomplishing here is nothing more than a duplicate of the source table. Wouldn't it be simpler to use the original for whatever purpose you have in store for the duplicate?

Edited February 19, 2016 by comment

[JoeHardstaff]

Hi everyone thanks for your help. 

 

I have cracked it. I used Aurigo's solution eventually. The reason it didn't work was because I needed to add another instance of the 'Results' table to link it to an existing table. Now works fine. 

2 hours ago, comment said:

For completion, here's how a script comparing two value lists in a loop would look;

Go to Layout [ “Target” (Target) ] 
Set Variable [ $sourceIDs; Value:ValueListItems ( Get (FileName) ; "SourceIDs" ) ] 
Set Variable [ $targetIDs; Value:ValueListItems ( Get (FileName) ; "TargetIDs" ) ] 
Set Variable [ $n; Value:ValueCount ( $sourceIDs ) ] 
Loop 
  Set Variable [ $i; Value:$i + 1 ] 
  Exit Loop If [ $i > $n ] 
  Set Variable [ $id; Value:GetValue ( $sourceIds ; $i ) ] 
  If [ IsEmpty ( FilterValues ( $id ; $targetIds ) ) ] 
    New Record/Request 
    Set Field [ Target::SourceID; $id ] 
    Commit Records/Requests 
  End If 
End Loop 

---
BTW, you didn't say why you need this. It looks like what you are accomplishing here is nothing more than a duplicate of the source table. Wouldn't it be simpler to use the original for whatever purpose you have in store for the duplicate?

The reason I didn't/can't use the originals is because if a staff member adds a new record to the layout based on the main students table  then that will add a new students record to the database which I don't want. So on things like the assessment tables I needed all pupils adding with the tables linked via student ID. So now if a new pupil joins the school I only have to click the add new pupil button to add them to the assessment tables and any other tables that I put in the script. 

I might adjust it so that when a new pupil is added to the database it then runs a script to add the new pupils to the other tables via the student ID using the method above. That's a project for the next half term I think!.

 

Thanks for the help though. 

[LaRetta]

 

23 hours ago, comment said:

If you set the StudentIDfk field in the target table to validate as Unique, Validate Always, you could then simply import the records from the Students table. This would skip all existing values and create new records only for the remaining ones. A similar result can be achieved by selecting "Update matching records in the file" + " Add remaining data as new records" for the import.

Hi Comment,

I did a double-take on your import step where you included a global field.  I see now that, since you only want to import the SourceID, and since FileMaker will not allow only a single field to import if it also is the match key, you need to import a second field so you used the global.  Very clever!

I prefer your first option over all other concepts provided in this thread ... simply set SourceID to unique and validate always then import using 'Add records' only and select the single studentID, which skips any students which already exist.  It is cleanest - no calculation, no custom function, no additional value lists ... simple and easy. :-)

While using the global works and will come in handy in some situations, it would trigger the modification timestamp on every record in the table so setting studentID to unique would be ideal here, since it will not modify other records.

Joe, you really should consider Comment's suggestion on setting StudentID to unique and using simple import; that is, if you really need to do it. Normally, a record is not added to a table until it is needed.  There is no need to add a student to the Assessment table until you create an assessment for that student and the same would be true for the other tables.

[comment]

4 hours ago, LaRetta said:

While using the global works and will come in handy in some situations, it would trigger the modification timestamp on every record in the table

Are you sure?

Added:  That is a valid point and a good reason to scrap this method.

 

4 hours ago, LaRetta said:

Normally, a record is not added to a table until it is needed.  There is no need to add a student to the Assessment table until you create an assessment for that student and the same would be true for the other tables.

Couldn't agree more.

Edited February 19, 2016 by comment

[LaRetta]

 

27 minutes ago, comment said:

Are you sure?

That appears to be the case when I tested it, Michael, at least with El Capitan and 11.0v4.  That is ... unless I am misunderstanding something here. 

ImportNewMOD.fp7

By the way, I believe this behavior of modifying records during an import even if they remain untouched is one of the problems with import based upon 'update' and not because of the fact that you are using a global field.  AFAIK, FM has always done this.

[comment]

For some reason, the modificationStamp  field in your file is listed as a target in the Import Field Mapping.

 

[LaRetta]

Oh.  I had originally used a copy of your file and had changed it quite a bit.  So when you asked for verification, I took your original file again and simply added the modification field and posted it here - I hadn't reopened the import map so I did not notice that it had mapped itself.

The mapping to modification timestamp you observe is the 'spontaneous mapping import bug' which existed in versions prior to 12, meaning that when I added the field, it mapped it.  But if you unmap it, the other records still will update their modification timestamp if you import.  It will effect all records in the found set at the time of import. 

That 'spontaneous import map' bug is thankfully gone in current versions.

As a matter of fact, NO records need to be imported ... the timestamps on all records will update.  Run it again after all three records have been imported to watch it in action.  This is why I prefer your first method of setting the sourceID (studentID) in the target table to unique, validate always ( a trick learned from you long ago ).  It protects from this 'update' occurring. 

Edited February 19, 2016 by LaRetta

[comment]

OMG, I've been testing this with the wrong file. You are absolutely right.

Edited February 19, 2016 by comment