Scripting a sync of table

[MikeKD]

HI folks,

I'm not sure if this is the right place for this, but it certainly involves scripting....

 

My colleague & I both use my dept assessment database.

At the moment, every few weeks, my colleague gives me a copy of her database and I import the new records, which adds any new assessments and comments.

 

However, the Pupil table contains fields that could potentially be updated by either of us (at the moment, any changes to this table get made on my copy of the database).

 

In the long term, I'm hoping to sync properly using GoZync (because it doesn't need FMS) or possibly Easy or MirrorSync. - I've already got UUIDs and modification date fields in each table.

 

However, in the mean time I'd like to develop my skills and work at a script to sync relevant fields in the Pupil table only.

 

As I see it, the work flow could be:

 

1. Import the Pupils table into an identical "holding table".
2. Compare the fields in the two tables:
   • ​If there's nothing in the holding table field, move on to next record.
   • If there's something in the holding table field, compare it to the original Pupil table, if they're identical, move on to next record.
   • If they're not the same do a calculation and set the field in Pupil table to have both contents if that's possible in a text field. Then move on to next record.

I think sync more often compares and uses the last updated record, but that's not so useful for us.

I was going to hard code the details for each field rather than use the "go to next field" step - that would give me more control and avoid the potential for disasters like renaming the UUID etc.

 

If my broad plan is about right, can I try to do all three stages using CASE, or do I need to use a few IFs?

 

Cheers!

Mike

 

[Wim Decorte]

Case() is not a script flow control, only a calculation flow control.

 

What you need is IF ... ELSE IF

 

Wouldn't it be much easier if you used FMS?

[MikeKD]

Case() is not a script flow control, only a calculation flow control.

 

What you need is IF ... ELSE IF

Oh, that's a shame - I was just starting to understand CASE!

 

It would definitely be better to have a proper sync from FMS on a server, but unfortunately that's not an option for a while; school leadership are worried about the amount of storage it might use. I'll lobby and imagine they'll come round sooner or later.

 

I suspect that the limited sync I'm proposing here might be easier though - and it means I don't have to link up to one of the proper sync solutions - although they all come with decent instructions; what they're doing is beyond my skills to understand.  I'm treating this as a learning step that also improves the solution I'm working with at the moment.

[eos]

I would either retrieve an up-to-date field list via ExecuteSQL(), or – if you want to constrain the update logic to certain fields – use the Design function FieldNames( ) on a layout that has (only) these fields. Then loop through the records and fields, applying Set Field By Name[ ] when appropriate:

Set Variable [ $fieldList ; ExecuteSQL ( " SELECT FileMaker Fields FROM YourTOName " ; "" ; "" ) ]
# Set Variable [ $fieldList ; FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
Set Variable [ $scratchTO ; Get ( LayoutTableName ) ]
Set Variable [ $targetTO ; hardcode the relationship's TO name ]
Go to Record/Request [ first ]
Loop
  # through records
  Set Variable [ $i ; 0 ]
  Loop
    # through fields
    Exit Loop If [ Let ( $i = $i + 1 ; $i > ValueCount ( $fieldList ) ) ]
    Set Variable [ $currentFieldName ; GetValue ( $fieldList ; $i ) ]
    Set Variable [ $scratchData ; GetField ( $scratchTO & "::" & $currentFieldName ) ]
    Set Variable [ $targetData ; GetField ( $targetTO & "::" & $currentFieldName ) ]
    If [ not IsEmpty ( $scratchData ) and $scratchDate ≠ $targetData ]
      Set Field By Name [ $targetTable & "::" & $currentFieldName ; List ( $targetData ; $scratchData ) ]
    End If
  End Loop
  Go to Record/Request [ next ; exit after last ]
End Loop  

Absolutely untested, and without considering all the nuances there are to syncing, so regard this as an approach that should work in the way you outlined it, and is reasonably short, for any number of fields. 

 

Note that you could end up with lots of duplicated data; simply a corrected typo would mean that you'd have essentially the same contents twice in that field. It would probably be a good idea to set an update check flag.

 

If my broad plan is about right, can I try to do all three stages using CASE, or do I need to use a few IFs?

 

If you're concerned with modification dates, you should – as shown – use the script If[], in order to only touch the existing record if it's necessary (i.e. there is something to write). Note that with this approach, you simply ignore the two cases in which there is nothing to write.

 

The other approach would be to use an unconditional Set Field by Name [ $targetTable & "::" & $currentFieldName ] and calculate its result with Case( ), e.g.

Case (
  not IsEmpty ( $scratchData ) and $scratchDate ≠ $targetData ] ; 
  List ( $targetData ; $scratchData ) ;
  $targetData
)

So if the contents are identical (empty or not), the field is set to itself. But I think the mechanism shown in the sample script is more economical.

 

As to calculations: I suggest you simply forget that If( ) exists, and always use Case( ) …

[eos]

Oh, that's a shame - I was just starting to understand CASE!

 

Just remember that there is no Case script step – as such … but as Wim noted, the script flow control Else[]...Else If[] is equivalent to Case() in a calculation. The same logic applies to both.

[MikeKD]

Thanks so much - lot's to think about there - it hadn't occurred to me to use a layout specifically for the purpose.

 

I'd spent my efforts on CASE because I'd read that a couple of you guru's used it more than IF...

 

It may be a little while before I've digested all this!!!

[LaRetta]

I'd spent my efforts on CASE because I'd read that a couple of you guru's used it more than IF...

 

Hi Mike,

 

Developers usually prefer Case() in calculations.  As you've noticed, there is no scripted Case().  To achieve the Case() construct ... multiple options within, use If/Else as:

If [ test criteria ]
... do something
Else If [ different criteria ]
... do something else
Else If [ different criteria ]
... do this instead
Else
... default step 
End If

It is understandable to get If() 'function' mixed up If[] 'script-step' also because you can use an If() 'calculation' within an If[] script step. :-)

[Wim Decorte]

 

It would definitely be better to have a proper sync from FMS on a server, but unfortunately that's not an option for a while; school leadership are worried about the amount of storage it might use. 

 

 

That is a weird argument.  In fact; with syncing you will end up using twice as much storage as if you had it once on FMS.  FMS itself does not add much in the way of disk space usage; it is your file that takes up space.  And it will whether you host it on FMS or not...

[bcooney]

Mike, don't be intimidated by EasySync. I believe using that framework to be a better use of your time than writing your own sync.

[MikeKD]

 

 

That is a weird argument.  In fact; with syncing you will end up using twice as much storage as if you had it once on FMS.  FMS itself does not add much in the way of disk space usage; it is your file that takes up space.  And it will whether you host it on FMS or not...

It's an argument I'll eventually win once they've seen how much FM is already doing for the dept. The data issue is partly because anything on the server is backed up by a RAID - so whatever I need is x4 or whatever.. Also, there's the possibility of using containers to record the kids work which would make a lot of sense long term.  I think they just said no because they don't know the full story yet.

 

 

 

Mike, don't be intimidated by EasySync. I believe using that framework to be a better use of your time than writing your own sync.

 

Unfortunately, until I've got FMS on the school server, EasySync / MirrorSync aren't options as they both require FMS to run.

GoZync doesn't though, and I am looking at that as an alternative. It seems that it may fit in very well with the constraints that I've got at the moment.

 

I'm not actually going to write my own sync engine, just script as way to merge in data from other databases in one or two tables....

[comment]

• If they're not the same do a calculation and set the field in Pupil table to have both contents if that's possible in a text field.

 

Are you sure that's what you want to do? If they're not the same, then one of them is wrong. Putting them together with no way to tell them apart seems like a recipe for disaster to me. But perhaps you are dealing with a different situation, since you say that:

 

 

I think sync more often compares and uses the last updated record, but that's not so useful for us.

 

If that's not useful to you, then it won't be useful to you once you get the server installed either. Note that the main idea of a served solution is not to reconcile discrepancies, but to prevent having them in the first place.

[MikeKD]

There are quite a few fields that have comments - about kids extra educational needs etc.  It's quite possible that two teachers could write different comments that are both valid.

1. Jim needs to sit at the front of the class.

2. Jim needs extra time for written work.

If only the last updated was used, we wouldn't know that Jim needed extra time.

 

Once proper syncing is in place I don't think this would be so much a problem, because 1. would be synced to both databases before 2. was written.

 

The problem I'm trying to overcome is that there is no real syncing; just merging data at the end of every 1/2 term. Obviously not very satisfactory (but still better than paper mark books or excel files that were never cross-referenced!)

I'm hoping that by scripting things to make the process quicker I can do it more frequently, better and wilt fewer mistakes.  And also learn a bit in the process.

[comment]

It's quite possible that two teachers could write different comments that are both valid.

 

IMHO, the proper solution for this is to have a Comments table, where each comment would be a separate record, with fields for date, author, PupilID, contents - and of course a unique ID. This is regardless of whether the solution is served or not.

[MikeKD]

I must admit, I was expecting that reply!!

 

I'm already planning to do that for some of the existing fields in the Pupils table (at the moment I've got instrument1 - Instrument4 fields which are obviously good candidates for this treatment.

I've already actually done that for end of term reports, class membership, cohort, attendance and assessments - that all works really well.

 

The advantage would be that if someone added comments they'd be in a separate record.  The reason I've resisted is that:

1. doubling up of comments like this would be very rare.
2. Having any comments at all is fairly rare - maybe 1 kid in 3.
3. these fields are used in portals in other table layouts.  But I suppose these same fields could become calc fields with a list of related comments? (this is what I'm planning to do with the multiple instruments).
4. I was trying to avoid too much complexity - my relationship graph looks like a gang of drunk spiders as it is!

[MikeKD]

 

 

IMHO, the proper solution for this is to have a Comments table, where each comment would be a separate record, with fields for date, author, PupilID, contents - and of course a unique ID. This is regardless of whether the solution is served or not.

I've done this now.

 

I need to do the same with the instrument fields, but that will give me the following problem:

In lessons, I'll from the Class Layout (based on separate Class Table) I have a portal of kids in that class. From there I can very easily add instruments played to each kid (just a case of selecting the kid and typing the instruments into the fields and then selecting the next kid.

If I put instruments into a new table, I can easily make a script that creates a new linked instrument record, but then I've still got to visit....

Will I be able to select a kid to bring up a related portal of their instruments within the Class (table) layout?

 

Having done that, I'm not sure I'll actually need the "sync" steps above; I'll just be able to import new records :-)

Cheers,

Mike

[comment]

Having done that, I'm not sure I'll actually need the "sync" steps above; I'll just be able to import new records :-)

 

Correct. The idea is to move this to the same workflow as discussed in the other thread.