Did a relookup change anything?

[brian rich]

If you use the re-lookup field contents function over a found set of records, is there any way to determine which records were changed as a result of the look-up, or do they all get changed?

 

I'm working with a legacy database which runs such a relookup process.  The found sets are quite large and at the moment, I have to process the whole found set afterwards as I don't know which have changed.  I'd like to reduce the found set to just those that have been changed.

 

Brian

 

 

[LaRetta]

Interesting, Brian.  One thing you can do is add a field which only changes if those records change according to tighter criteria you specify such as your script running.  For example, if a field called Type might change then you can create a flag field which has an auto-enter (REPLACE) with:

 

Let ( trigger = Type ;  Case ( Get ( ScriptName ) = "Relookup" ; 1 ) )

This will only set the flag to 1 if Type is changed AND if the script name running at the time is "Relookup".

 

I am on iPad so I cannot test but this should work.  Note also that the script name is hard-coded and that is not good; not sure how to protect from that breaking since we don't have a ScriptID function.  I may think of it later, LOL.

 

UNTESTED, BACK UP FIRST.  :-)

BTW, you can include many fields in the trigger, such as Trigger = Type & Category & ...

Edited November 14, 2013 by LaRetta

[LaRetta]

BTW< no matter which process you use, you will need to protect from record locking.

Also I was sloppy … it can be:

Let ( trigger = Type ;  Get ( ScriptName ) = "Relookup")

[comment]

is there any way to determine which records were changed as a result of the look-up, or do they all get changed?

 

AFAIK, a relookup re-enters the values into the foreign key field. As a result, all records in the found set will be modified, regardless of the previous value in the lookup field. I don't think you can tell which records have looked up a new value, unless you keep the old one somewhere.

[bruceR]

Well, another approach is to change the process. The relookup is done by a script? Change the script to walk the record set and omit all records where current value = lookup value.

 

If this is something like a price, seems like it would be easy to do, you could know what the related product price is compared to the current item price, for instance.

[comment]

The relookup is done by a script? Change the script to walk the record set and omit all records where current value = lookup value.

 

Indeed - and even without a script, you could use a calculation field to compare the current value with the related one, then constrain the found set to records that return false.

[brian rich]

Ladies and Gentleman, thanks for all your responses. Yes, since I posted this, I have convinced myself that all records in the found set are modified when the re-lookup is performed, so some strategy is needed to compare before and after values in the found set and only process records where those are different.

The background is that the database is a parts list, where first level subassembly parts are described in a set of repeating fields with 50 repetitions in each; each subassembly part has it's own unique record in the database. The relookup uses a self-join between the sub-assembly part number and the record part number, so details of each sub-assembly can be pulled from the corresponding part record. Five repeating fields are populated using this lookup. The script which does the relookup actually does the relookup twice, which has the effect of pulling second level assembly changes through to the parent part. I need to know which records were changed by this script.

@BruceR and @comment - I like the idea of walking the found set and dropping any records which won't change, but am somewhat daunted by a method of detecting changes in any of the 50 repetitions of each of the five repeating fields, especially when the relookup is done twice. I can't see how to do the simple A = B check where repeating fields are involved.

@LaRetta - Let ( trigger= Type & Category & ...; Get ( ScriptName ) = "Relookup")

Had to think about this....it works because the auto-enter calculation will only refresh if Type or Category etc changes, so the variable trigger gets a value, then the result of the function is true (or 1) if the script name is "Relookup", otherwise it is false.

Does the field FLAG will need to be cleared to FALSE or before the re-lookup occurs? I think it will only refresh if a change occurred, otherwise it will retain it's previous value so it will need clearing before the script does the first relookup, but NOT before it does the second.

Can the Let() function be defined when Type and Category etc. are all repeating fields without having to define each repetition individually?

Multi-user locking should not be an issue as I want to do this processing as a server side script overnight, when there are no users logged in.


Brian

[comment]

I can't see how to do the simple A = B check where repeating fields are involved.

 

Try:

List ( SourceTO::SourceValues ) = List ( TargetTO::LookupValues ) 

Note that only non-empty values are compared, so that [ A | B |   |  C ] will be considered equal to [ A |   | B |  C ].

 

 

I didn't get the part about doing the relookup twice.

[brian rich]

 

I didn't get the part about doing the relookup twice.

 

It's all about multiple levels of sub-assemblies and getting any changes to ripple through correctly.

 

Part 1 consists of sub assemblies 10,11, and 12.  Part 10 costs $1, part 11 costs $2 and part 12 is a subassembly costing $5.  The total cost of Part 1 is therefore $8, the sum of the costs of the sub-assembly parts. 

 

Part 12 consists of subassemblies 20 and 21. Parts 20 and 21 cost $2.50 each.  (The costs are worked out using a stored calculation on the part which sums the costs looked up from each sub-assembly part)

 

The cost of part 21 changes from £2.50 to $5, so I record that change on part 21. 

 

When I do the first relookup, the cost of part 12 updates to $7.50.  If part 12 gets relooked up before part 1, then part 1 will relookup the new cost from Part 12  and it's cost will change from $8 to $10.50.  If part 12 gets looked up after part 1, then part 1 looks up the old cost, and stays at $8. The second relookup ensures that the change is calculated whatever the order of the records.

 

It's horrible but it works - as long as you don't have more than two levels of subassembly.  I suppose if you repeat the relookup enough time, you could go to any number of sub-assembly levels.    

 

I'll try the list comparison  idea - should be an interesting exercise

 

Thanks

 

Brian

[brian rich]

@comment  - using the List(SourceValues) and List(TargetValues) doesn't seem to work because the order of the list on the source side is dictated by the order the vlaues are placed in the repeating field, whilst the target values are sorted in some other order - I guess the order of the records in the file.  So the list comparison becomes something like "A¶B¶C¶" = "B¶A¶C¶" which returns false.  

 

Brian

[comment]

@comment  - using the List(SourceValues) and List(TargetValues) doesn't seem to work because the order of the list on the source side is dictated by the order the vlaues are placed in the repeating field, whilst the target values are sorted in some other order

 

LOL, this gets curiouser and curiouser... Can we at least assume that the number of values is equal? Or, if not, that there is some consistency regarding which side has more values?

 

---

One more: I understand that there is no guarantee that the values (prices, IIUC) are unique - i.e. you could easily have {A, B, C, A} compared to {A, A, B, C} - and you say this is supposed to be considered as equal; but this could also turn out to be a coincidence - and I am not sure you can "afford" such coincidence, once you multiply the prices by quantities? If I am wrong about this, perhaps you could just compare the sum of the values?

Edited November 15, 2013 by comment

[LaRetta]

@LaRetta - Let ( trigger= Type & Category & ...; Get ( ScriptName ) = "Relookup") 

Had to think about this....it works because the auto-enter calculation will only refresh if Type or Category etc changes, so the variable trigger gets a value, then the result of the function is true (or 1) if the script name is "Relookup", otherwise it is false.

Does the field FLAG will need to be cleared to FALSE or before the re-lookup occurs? I think it will only refresh if a change occurred, otherwise it will retain it's previous value so it will need clearing before the script does the first relookup, but NOT before it does the second.

Can the Let() function be defined when Type and Category etc. are all repeating fields without having to define each repetition individually?
 

 

Hi Brian,

 

You have the idea.  It will only update to 1 when the script is running and only when the value changes ; other modifications should not affect it.  

 

However, both Bruce and Comment approached it a different way and did not consider my approach an answer so you would be wise to listen to them (I'm sure there are reasons this approach wasn't pursued but I can't currently test my idea).  I had to send my Mac to Apple Care so I've been without a system and I'm a bit buried right now.  I'll catch up on this thread tomorrow.

 

One thing rocking' cool ... migration assistant.  Backed up my 15" onto Time Machine and then installed settings, programs, everything onto my new 13" retina and it only took an hour!  Now I'm back up!  Macs rock and I'll never be without one again.  What incredible service they provide.  Incredible.

 

added bold

Edited November 16, 2013 by LaRetta

[LaRetta]

Upon reading the rest of the threadl and without testing my concept, I think their approaches are better.  :-)

[comment]

This will only set the flag to 1 if Type is changed

all records in the found set will be modified, regardless of the previous value in the lookup field.

However, both Bruce and Comment approached it a different way and did not consider my approach an answer so you would be wise to listen to them (I'm sure there are reasons this approach wasn't pursued but I can't currently test my idea). 

 

Perhaps I have misunderstood your idea - I am perfectly willing to wait until you can test it yourself.

[LaRetta]

I will not need to test it if it modifies the record anyway (I missed that, my apology).   But of course I will still test it to understand the behaviour.  :-)

[brian rich]

 @comment:  Unfortunately, you can't even assume that the lists are the same length or even in the same order.  The sub-assembly list is presented to the user as a set of 6 x 50 repeating fields arranged as as a grid on the layout, and the user may (bless him or her) fill in the part number on every other line because it spaces it out nicely and is easier to read - hence the possibility that a source list based on the part number could contain null lines.The target list is a generated list which only gets populated when a repeating field has a matching lookup, so it won't contain any null lines, and the order will established by Filemaker  

 

@LaRetta:  Re-reading this thread this morning and seeing your Let(Trigger... suggestion reminded me of Ray Cologon's Ultralog function. In particular, this function records 'changes' in fields and is automatically triggered when the record is modified.  I'm going to take another look at it and see if I can somehow adapt it to this problem where I merely want to flag that a change occurred in the specific repeating fields when the particular script ran.  The down-side is that you have to specify the fields you are 'watching' which in this case is 300 entries - 6 fields with 50 repetitions each. I suspect this will give a noticeable performance hit when the function gets called, so I'd also like to find a way of inhibiting the function so it only activates when the specified script is executed.  If anyone can suggest how to do this, I'd be interested. 

 

Thanks for the suggestions to date

 

Brian

[comment]

I am afraid you are losing me (or perhaps you have lost me already on the double lookup - I can't pretend I understood that). In general, you can use the FilterValues function to compare lists of values with no regard to their internal order. If the values in each set are unique, then =
 

FilterValues ( ListA ; ListA ) = FilterValues ( ListA ; ListB )
and
FilterValues ( ListB ; ListB ) = FilterValues ( ListB ; ListA )

will return true only when both lists contain the same values. You don't need to be concerned with blank values, since the List() function will exclude them. However, you do need to consider duplicate values, because the above test will consider {A, A, B, C} to be equal to {B, B, B, C, A} which is consistent with set theory but may not fit your needs.

[brian rich]

@comment - The cost of a Parent Part is calculated by summing the cost times quantity of it's child parts. If a child part is itself made up from several child parts of it's own  then I have to work out the cost of the child part from it's children before I can work out the cost of the parent. The double lookup is needed if the cost of a grandchild item changes.  Parent parts and child parts are all defined in the same parts table and the look-up is a self join back into the table;  if the child part which has children of it's own is located in the table earlier than the parent part, then it's cost would already be correct when the parent part was evaluated. However if the child part comes after the parent in the table, then it's cost will not be updated so the parent cost will be incorrect the first time through.  The second relookup corrects this as by this time the child cost will be correct from the first lookup when the parent comes to look it up.

 

Thanks for the suggestion of the FilterValues. I'm not quite sure how to apply it though.  In order to work out what the relook-up changed, I need to set a flag which says that 'values in this record changed'. The only way that I can see to do this is to go through the found set prior to the relook-ups and store the previous value(s) so that afterwards I can compare new and old values to set the flag.  The problem is that to do this I'd need to process in advance the complete found set to store the previous value.  I don't think I can get the re-lookup itself to store the old value or evaluate the flag setting. So I'm almost back to where I started in that I have to process the full record set beforehand in order to reduce the processing afterwards.

 

Looks like I'll have to settle for processing the whole of the found set each time. 

 

Thanks for all the useful ideas

 

Brian

 

PS:  adapting Ray's Ultralog script does record the change from the re-lookup but I still needed to preprocess the found set in advance so I could see where the change occurred.

[comment]

It looks like you have made a major step backwards here. What Bruce suggested was to flag (or omit) the records unaffected by the relookup before performing the relookup. At that point, both the "old" and the "new" values are known - the existing values are stored in each record and the upcoming values are available through the relationship. So it's not a matter of 'values in this record changed' but rather 'values in this record will change'.

I thought all this was already settled, and the only problem was how to compare the "old" vs. the "new" - since you said the comparison needs to ignore the order. That's what my last post was about. Once this is worked out, the only "processing" required is to perform a find. It might be a slow find, since it will be based on an unstored calculation, but I don't see how it could possibly be anything less than that.
 

 

---
I am sorry you went through the trouble of explaining the double lookup again; that was not my intention. I don't think it's important that I understand it (though I think I do now, despite my effort not to... ), since it's not really relevant to the problem at hand, is it? OTOH, it might be, because there's no way to see the "new" values of the second relookup before performing the first one.
 

[brian rich]

 

OTOH, it might be, because there's no way to see the "new" values of the second relookup before performing the first one.

 

I think it was the double look-up (which you weren't aware of at that time) which made me think Bruce's suggestion of walking the found set in advance to flag or omit records wouldn't work. However, maybe walking the set in this way could be quicker - must be worth a punt so I'll try it and see what happens - thanks for staying with me on this.

 

Brian