ObjectID CF instead of hard-coding

[David Nelson]

I have been trying to figure this out. I have created the custom function and it is fine I'm sure because I just copy/pasted it. Then I go to script or calc, I tried both, and I need to know what to put in its places of object and type.

ObjectID ( object ; type ; file ; layout )

I try attaching object name to first name field and trying this: ObjectID ( "FirstName" ; "T" ; "" ; "" ) ... I try a hundred things, anything to just get a result. I can't figure how it works because I am not knowledgeable enough yet but I want to still use it. Is that wrong? Here is the full thing and thank you so much for reading about my need and maybe helping.

/**

* =====================================================================

* ObjectID ( object; type; file; layout )

*

* PARAMETERS:

* @object supply either the name or internal id of an object

* (see type variable for supported objects)

* @type (enum) One of the following

* "Table", "Layout", "Field", "Script", or "ValueList" or (T,L,F,S,V) for shorthand

* @file [optional] specify the name of the file (if using multiple files) - empty implies current file

* @layout [optional] required only if @type contains "Field".

* Empty implies current layout (or the field table occurrence, read the note below).

* The layout should be tied to the table occurrence of the field

*

* RETURNS:

* (mixed) Internal FileMaker ID if name of object is supplied

* and inverse if ID of object is supplied

*

* DEPENDENCIES:

* None

* AUTHOR:

* Fabrice Nordman

* NOTES:

* for fields (_TLFSV = "F"), if you use the full field name

* (table::fieldname) AND an empty var.layout parameter, the

* function will assume you are referring not to current layout but

* to the table occurrence found in the _TLFSV

*

* =====================================================================

*/

Let ( [

file = If ( IsEmpty ( file );

Get ( FileName ); // use current file name... otherwise

file

);

layout = Case (

IsEmpty ( layout );

Case (

PatternCount ( object; "::" );

GetValue ( Substitute ( object; "::"; ¶ ); 1 );

Get ( LayoutName ) // default

);

layout // default

);

layout = Case (

Int ( layout ) = layout and Length ( layout ) = 7;

ObjectID ( layout; "T"; file; "" );

layout

);

var.object = object;

// Allow type to be a single character (T,L,F,S,V)

var.type = Left ( type; 1 );

var.type = Choose( Position ( "TLFSV"; var.type; 1; 1 ) - 1; "Table"; "Layout"; "Field"; "Script"; "ValueList" );

var.object = Case ( // remove the repetition number

var.type = "Field" and PatternCount ( var.object; "[" );

Left ( var.object; Position ( var.object; "["; 10000; -1 ) -1 );

var.object

);

var.object = Case ( // for fields, do not take TO

var.type = "Field" and PatternCount ( var.object; "::" );

Replace ( var.object; 1; Position ( var.object; "::"; 1; 1 ) + 1; "" );

var.object

);

var.endOfString = "( \"" & file & "\"" & Case ( var.type = "field"; "; \"" & layout & "\"" ) & ")";

var.names = Evaluate ( var.type & "Names" & var.endOfString );

var.ids = Evaluate ( var.type & "IDs" & var.endOfString )

];

Case (

var.object = GetAsNumber ( var.object );

// Convert ID -> Name

Case (

not IsEmpty ( FilterValues ( var.object; var.ids ));

GetValue ( var.names; Let ([

_text = var.ids;

_item = var.object;

_adj = ¶ & _text & ¶

];

PatternCount ( Left ( _adj; Position ( _adj; ¶ & _item & ¶; 1; 1 ) + 1 ); ¶ )

)

)

);

// Convert Name -> ID

Case (

not IsEmpty ( FilterValues ( var.object; var.names ));

GetValue ( var.ids; Let ([

_text = var.names;

_item = var.object;

_adj = ¶ & _text & ¶

];

PatternCount ( Left ( _adj; Position ( _adj; ¶ & _item & ¶; 1; 1 ) + 1 ); ¶ )

)

)

)

)

)

/*

Original function by Fabrice Nordmann

Reformatted by Matt Petrowsky

Avoids hard-coding in FileMaker by using IDs instead of names

v.1.6, Mar 2009

Accepts field parameters such as FMvar.name_id ( 1065234::24; "F"; ""; "" )

v.1.5.2, Feb 2009

Removes the repetition number if a fieldname with a repetition number is passed ( field[n]). Does not handle field names with [ anymore.

v.1.5.1, Dec 2008

Documentation update (thanks to Kevin Frank's comment)

v.1.5, Oct 2008

Documentation error fix

Returns an empty result if item not found

v.1.4, Aug 2008

Bug fix (major) : could return erroneous result if an item ID was found in another ID.

v.1.3, July 2008

A field ID can be obtained by passing its full name (with table occurrence) in var.name_id and leaving var.layout empty

e.g. FMvar.name_id ( "myTable::myField"; "F"; ""; "" )

v.1.2, June 2008

Doesn't require Position value

v.1, May 2008

Requires: PositionValue ( _text; _searchValue; _start; _occurrence )

// Test code for raw data

Let ( [

f = Get ( FileName );

l = Get ( LayoutName )

];

List (

"--- TABLES ---";

TableNames ( f );

TableIDs ( f );

"--- LAYOUTS ---";

LayoutNames ( f );

LayoutIDs ( f );

"--- FIELDS ---";

FieldNames ( f; l );

FieldIDs ( f; l );

"--- SCRIPTS ---";

ScriptNames ( f );

ScriptIDs ( f );

"--- VALUE LISTS ---";

ValueListNames ( f );

ValueListIDs ( f )

)

)

*/

[bruceR]

I suggest you attach a simple example file that includes the custom function and demonstrates how you are trying to use it.

[Raybaudi]

All those "[/color]" aren't suppose to be there.

[David Nelson]

Hi Daniele,

Those color tags aren't in the version I am using. They appeared when I pasted it here inside the CODE tags.

Hi Bruce,

All I want is to know how to get ANY unique ID using this calc. ANY working example at all. No example and result has been provided like on Comment's custom functions which are always easy to create even if I don't completely understand them because they show example and result. So for example if I want to know the ObjectID() of a table occurrence named Invoices, how do I find out? I assume the calc or script must be in the Invoices table to even work. But how would such a calculation look which is using this CF?

I have been unable to come up with a single working example. The original CF is from here. I copied it and FM accepted it in my calc dialog when creating it. It is a calc I cannot create.

https://github.com/petrowsky/fmpstandards/blob/master/Functions/ObjectID.fmfn

Ps I can't demonstrate how I am trying to use it because I can't create a calc using it.

[Lee Smith]

Sometimes the Code function does this. I just remove it, went to the source, and then pasted it straight into your post.

[David Nelson]

Hi Bruce,

Here is a file with the custom function in it. I want to identify the ID of a table occurrence without hard-coding the table occurrence name, like is suggested. I cannot write the calculation to get a result. I can't even get out of the calc dialog. I just need ONE example of what works with getting a table ID. Even in what I tried, it appears to require my hard-coding the name in the calc and that can't be right. And how would I get the ID of a table which is not related to the current table? If I have to hard-code here then it defeats the purpose of this CF which is to eliminate the need to hard-code.

Thank you for considering this. As for purpose, it will be to relate two tables or fields by their IDs so I do not have to hard-code them. It is the first time I have heard of this concept of using internal identifiers and I now want to use this in everything where I normally type a field's or table's names.

ObjectID.zip

[comment]

Try calling the function as =

ObjectID ( Get ( LayoutTableName ) ; "Table" ; "" ; "" )

Make sure the calculation field is unstored.

As for purpose, it will be to relate two tables or fields by their IDs so I do not have to hard-code them.

I am not sure what you mean by that.

Edited November 4, 2012 by comment

[David Nelson]

Hello Comment,

 

That works, thank you so very much.  I could not get started in understanding it.  It took a bit to figure out all of it especially between switching between different tables but now I can even get the table name without hard-coding and I am stoked!  It looks like this

 

ObjectID ( ObjectID ( Get ( LayoutTableName ) ; "table" ; "" ; "" ) ;  "table" ; "" ; "" )

 

and I can do the same with layouts although I can't use calc because it doesn't know the layout but it works great in scripts where it can look to the layout to see where it is.  Thank you!

 

Oh on this part "it will be to relate two tables or fields by their IDs so I do not have to hard-code them."  It was so that I could join two tables by their ID so allow creation writes the table ID into the other table as a number and it will not fail me even if I change the table name. 

[comment]

It was so that I could join two tables by their ID

 

I am not sure what you mean by that either. Where exactly do you have the opportunity to hard-code a table name?

 

 

BTW, are you aware that table (and other objects) IDs are file-specific?

[David Nelson]

I am creating records of changes from several tables to one log table. They are all joined to this archive. I join like this

Contacts::ContactID = Log::externalID

AND

Contacts::tableID = Log::tableID

which i set table ID as number to keep size small. I use ObjectID to then get the table occurrence name from that when I need to Set Field By Name when attempting to pivot. This is in case the names are ever changed it won't fail because I am using the same scripts for writing from all the tables to this log and I did not want to do this

Get(scriptparameter) & " Log::log data"

Where the parameter was the table name. A script parameter is a hard-code and so is Set Field By Name. And now I wanted to use same to write the records but it does not seem to work. It just won't pivot and I do not understand how it pivots or what IS a pivot and google doesn't help. Oh and I use it to Go To Layout number by calc.

IDs file specific? No I did not know that. So if I cloned this file, my table ID of 106695 would become a different number? If true then i would no longer know the table name of those records in the archive. i suppose i could write the names to field in the archive table but then I am right back to square one. Btw I just discovered another CF for getting IDs from NightWing. I will have to ask Dan Smith who suggested ObjectID or do you know - you probably do.

"File-specific" is concerning if i understand. I was going to use it all through my file and not just this archive process. I have put so much into this and I am almost done with this archive and even though I have not succeeded in writing records pivoted yet I am so close I can taste it. Or am I mega-streaming off base somewhere here?

Now I really hope you say more since this has struck concern in me.

[comment]

David,

 

I honestly have no idea what are you trying to accomplish here. When you establish a relationship between two tables (or rather two TOs), using a pair (or several pairs) of matchfields, nothing is hard-coded. You can freely rename the TOs and the matchfields involved, and the relationship will keep on functioning. I could be well missing something here, but it seems to me you are working very hard to solve a problem that doesn't exist.

[comment]

I gave this another thought and I think I finally see what the purpose of
 

 

AND
Contacts::tableID = Log::tableID

might be. When you have a table (Log) that is child of several parents (Contacts and say Products), you might consider keeping a separate foreign key for each parent. So your relationships would look like this

Contacts::ContactID = Log::ContactID

Products::ProductID = Log::ProductID

etc.


Alternatively, you could use a unique prefix for the serial ID of each parent table, so that the Log::ExternalID field would contain "C156" when the parent is Contacts, and "P156" when the parent is Products.


Finally, there is nothing wrong with using a dedicated pair of matchfields to identify the parent table as your example shows. But then your table ID value should be (as is always true for a primary key) completely meaningless and independent of anything that might change.

 

A table's ID is issued by Filemaker arbitrarily (in creation order, I believe) and there is no guarantee it will remain the same when you move your data to another file, where the order of table creation might be different. I would just pick my own table IDs (they can be anything as long as they are unique) and "hard-code" them.

[David Nelson]

Yes that is exactly it. I have eight tables to write to Log and I was getting off track but if I use different keys it will be simple to write them through. I really think I understood you perfectly. And when i want to Get a table name then, as you say, we know it because of our perspective by being on a layout which tells us, just use Get(LayoutTableName). That is probably why they are named Get(). Yep I am genius.

I was trying to make a 'something' unique so that I did not have to stress about changing the name and having it break. Does that sound like anything familiar? Gosh maybe a table? Oh the irony. Where were you when I was trying to teach logic to my son when he was 15? I'm off, feeling much lighter. And again I am most grateful for your time and brainpower.

[David Nelson]

I really think I understand your suggestions and I have made those changes but I am still stuck at the point where I thought I was hard-coding too much and where I headed off-track.  If I cannot figure how to use this process with several tables instead of just one, I may not be able to use it at all and I really want to use the Nightwing license. 

 

I will be running this Archive script in the Ultra Log TOG standing on the People Log layout and writing to create the records.  It is called a pivot if that makes a difference.  I will loop create records from a text auto-enter LogData field with allows created on. Then I will switch to Activities_Log table and loop also.  This will be done at night.

 

I would rather not have to create 8 identical scripts, one for each table.  One line needs to use 'by name' on both sides as

 

Set field [ LogWrite_People::PeopleID ; People_Log::PeopleID ]

 

How can I make script like this dynamic depending upon where I am standing or can I?  Is it even possible?  I suppose I should just write 8 scripts and be done with it but it is such a shame when this entire process will be identical for every table.  If you would again be so kind, and if I have not worn out my welcome, how would you handle this script part if you were me, Comment? 

 

I can get the Go To Layout parts because I understand that now.  It is the center where it sets all those fields and sets the IDs that I am unsure of.  I even know how to set field by name but that hard-codes so much.

Archive.zip

[dansmith65]

I think the attached file will do what you want. The key changes I made are:

1. All tables that need logging are related to the same LogWrite table
2. When accessing log-related fields, use GetField () to get a field by name, based on the current context (layout).
3. Add a field to each table that needs logged that contains the table name, so that it can sent to the log table via a relationship
4. Use a single ForeignKey field in log table

This isn't the only way to accomplish what you are wanting, but I think the basic idea should work. I hardly tested this at all, so it will likely need some work before it's complete - I was just trying to give you the basic idea.

ArchiveDS.zip

[comment]

I've only glanced at this, but:

 

I believe Source should be an unstored calc instead of auto-enter - there's no need to store it in each record. I see that Dan has done this with the TableName field, but that should be instead, not in addition to Source.

 

I am not sure what is the purpose of the gArchive# = UltraLogID predicate.

[LaRetta]

It is because if gArchive# is empty (and since it is joined to the primary key of the UltraLog), it does two things):

 

1) backfills the gArchive# with the child key and only THEN it has valid relationship so

2) treats every write to UltraLog as unique and adds a new record.

 

Here is simple test ... clear the gArchive and run the script. :^)

logTest.zip

[David Nelson]

Hi Dan! 

 

I just got home and I took your file apart and it has helped me a lot.  I have it all working now and with only one script and no If/Else stuff.  Much appreciated. I was getting tripped on GetField too.

 

Hi Comment,

 

I have made that change also thank you so much.

[David Nelson]

Hi LaRetta,

 

I think this is the neatest thing I have learned in FM so far and I have learned many very neat things.  Can you explain why the global gets the id from the log?  I do not see script or trigger or auto-enter.  Is this the pivot that is referenced in the log demo?  However it works it works great.  Thank you for providing that simple example because I could not see that when I was working in my file. 

I have to ask also, you mentioned creating two records simultaneously in two tables. And now it is gone from your post.  Can you say more about that?  Is it hard to do?

[LaRetta]

Hi David,

 

Well, I deleted the sentence about another technique because it really didn't have anything to do with the title (or main focus) of the thread.

 

But here is a file I had created for a post few months back and never posted.  It shows how an ID can flow backwards even if not using a global.  In this example, Allow Creation is on between Assignments and Crew (on the Crew side)  ... no surprise there since it is a 1-to-many and new Crew is added in the portal.  But I also have allow creation on between Crew and Members (on the Members side).  Then the Members portal to the right of the Crews portal is filtered to 0 which means that no records relate.  You can select crew from the left portal and if they are not in the list, add a new worker to the right.

 

The new worker is created and it automatically backflows and creates a crew record as well.  No scripts, no triggers, no additional table occurrences and not using a global at all.  Two fer, LOL.

 

The UltraLog and using gArchive# to create new records is the idea of Ray Cologon, PhD (aka CobaltSky) and NOT my idea (see NightWing website for details on UltraLog link above).  Using filtered portals and backfill (I call it that; not sure what its correct name would be) to create new records I haven't seen anyone else use but that does not mean they haven't.  And I am sure Michael knows this concept as well. 

 

I do not know why it is called pivot, sorry.  Usually a pivot table would take the results and turn and write elsewhere, pivoting the criteria as it goes.  I do not see a pivot in what you have here nor in the original demo file if Ray did it and calls it a pivot then it must be a pivot. 

 

Added blue for clarity.    ;-)

TwoForOne.zip

[David Nelson]

Hi LaRetta,

It just creates a crew record also when you create the worker in the other table. Strange. It appears to back write in both the global LogTest and TwoForOne but in LogTest it sets a global back in the originating table and in TwoFor it actually creates a record in an intermediate table. I am glad I asked because I had no clue you could do things like this. Maybe this is the pivot.

Both these ideas will come in very handy. I really like things that happen automatically without script or extra requirements. I still do not understand how it works no matter how much I stare at it. Can you or Comment explain more on how this works? Maybe it is like explaining gravity but I have to ask.

[LaRetta]

I do not know why the backfill OR gravity works. ;^)

If anyone can explain the theory it would be Michael or Ray. All I can say is that it must reconcile the ID so it can complete the action.

[dansmith65]

I'm not sure if David is referring to the "Magic Key" method or not, but if he is, I think this blog post explains it pretty well: http://www.filemakerhacks.com/?p=2364

[David Nelson]

Hi Dan,

Thank you for responding. LaRetta mentioned Magic Key and I have already read that technique but when I read it months ago I did not understand it. That explains the Ultra Log and I won't attempt to guess who came up with it originally. It seems Nightwing and Mr. Frank both were using it early 2009 so I now know I can trust it. But that just shows that it works and does not explain why that I can see.

It is one thing to back write an ID into a global but it is another to back fill and create two records, one in the middle table as TwoForOne shows. I looked and allow created is not on in the crews table from the members side so I do not see how it can even do it.