Dynamic Relationship?

[LaRetta]

A short one. One FM table with existing records called [color:blue]Incoming with fields: field1, field2, field3 ... All fields standard text fields. First Incoming record may or may not be the field names. If record1 is field names (and I understand User must choose to say which), display them 'as field labels' and omit/delete from the record set. If not, allow User option to 'name' them (if they wish). Another FM table called [color:blue]Receiving with fields: CompanyName, FirstName, LastName, Address1 ... etc.

User views the record-set in side-by-side list (on form hopefully) and MATCHES Incoming fields to Receiving. Various relationships exist from Receiving to real tables: Customer, Address, Numbers, people. and provides views of similar data based upon multiple criteria. I won't go into why (aren't you glad ). I just need to know if it's possible for a relationship to be created between Incoming and all of my regular tables THROUGH a Receiving table. I'm rich on ideas but blank on method.

It seems I read something about using Evaluate[] to dynamically name tables and fields. But I remain blank. And how to allow point-click for User to match and how to turn the first record into a label (if needed). Kick start me, will ya please?

LaRetta

[Søren Dyhr]

Maybe it's because you're tied up to a way too rigid tablestructure with too many fields in each table ...you have probably seen it before - but you might benefit from seing it again:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

--sd

[LaRetta]

Yep Soren, seen it, thank you! I was hoping I wouldn't have to go into specifics. It's not the number of fields, it's that I want to control User importing and field matching. If Contact information is turned into flatfile, it's approx. 50 fields possible they will need to import (but may only be 15). I don't want them to wade through Import dialog but rather generic 'import on creation order' stuff.

I am attempting to provide a buffer from Sales Reps bringing in new Contacts into my normalized system. The purpose of Receiving is to 1) force decisions on matching fields, 2) When they point field6 to PhonePrimary, relationship will display Numbers::Number (and other Contact data) so they can see if it exists. Same with many other relationships based upon various matches. If not, flag as New Record to add but if the record is there (same name and matching on phone), there may be field8 with FaxNumber which can be 'moved' into Numbers. Click checkbox on Record > Add New Record; click checkbox next to certain field names > Replace or AddTo existing 'real' data.

This is in place now from Receiving on ... and it provides great views of incoming information. I have scripts which auto-import Prospect lists now where I control the imports. But Users will be pulling lists with different field names and field orders in. And it's the reviewing of new lists against existing data and forcing determinations BEFORE it is pulled in, that I'm looking for. AND ... I REFUSE open my field definitions for their import matching. I want them to import generically and then match them from within the ease of FM and NOT import dialog. There are only certain fields they can import into. One mis-matched field and a Numbers table can contain garbage; or field names get pulled in as records, or they import a date into my CustomerID, etc. See where I'm coming from here? Once matched to receiving, my process will make it so. But not until.

I simply need Incoming:Field1 = Receiving::CustomerName, etc. This structure will also allow self-analysis of my existing data. For instance, export as flatline, import and compare. Non-exact duplicates will be spotted quickly and will assist in leaning-up our existing data.

I don't want them importing directly in Receiving because: 1) Receiving will contain TempID which is copied into Contacts on New Record creation. ContactID (based upon this temp relationship) is then written BACK to Receiving. This makes moving all child data straight pulls into the real child tables. I don't want them importing into TempID or ContactID, etc. And there is no way to lock Users from certain fields during import, is there? This is why they will have their own Incoming table. I want to take it from there.

So I guess you're saying it can't be done?

LaRetta

[comment]

IIUC, you don't want any calc/global etc. fields in the Incoming table - just plain data. Is it acceptable to move the imported data to an Intermediate table, where there can be calc fields?

[LaRetta]

Is it acceptable to move the imported data to an Intermediate table, where there can be calc fields?

Yes, Michael and that's the purpose of Receiving. In fact, I considered that Receiving only needs to be calculations - acually placeholders or fields themselves. I don't particularly want to copy Incoming records into Receiving. But I want those Receiving fields protected. After all data is handled by the User, scripts will run various validations, create the new records, modify existing data, etc. Receiving is just a pointer. I hope I made sense. But if Receiving doesn't have records, how can User specify (flag) what to do on each record? I considered looping through the fields and creating records in Receiving (after User had named the fields ... or ... from the first record if it was field label). But the matching still eluded me. :blush:

[comment]

The thing is, if there are no "real" records in Receiving, the calcs have nothing to hold onto, so to speak - there is no ID in Incoming, so how do you browse that, when you cannot specify current record?

I think the same import script could first import into Incoming, then import from Incoming to Receiving. It would be completely transparent to the user - in fact the users should be under the impression that they are importing into Receiving. The only time they see anything connected to the Incoming table is while mapping import fields.

[LaRetta]

Yes! How to map! How to tell script or relationship field1 is CustomerName!?

Yes tho! You are with me on it!!

Oh. Except that I don't want all records created into new records. User must view, compare ... it will also be used to bring in partial information! Users may disregard several records, import only certainly fields on some, etc.

How would scripts know what to do without User intervention at this point?

Sorry, I'm excited and trying not to talk much.

Edited July 21, 2005 by Guest

[LaRetta]

Oh! Let them Import into Incoming - exact same field names. Move it via script to Receiving - let Users manipulate it THERE (view records, decide what to do with records/fields)! Then move it on! Gotcha! I think!

Geez. But I also didn't want them to have to deal with Import dialog - that's why field1 generic structure. Boom. Import.

Edited July 21, 2005 by Guest
I got overly excited and didn't think!

[comment]

See the attached - it's a bit raw, more like a bank of possible ideas.

assignableImport.fp7.zip

[LaRetta]

WOW. This is perfect. Wow.

GetField!! You saw my vision better than I did! It is E-X-A-C-T-L=Y what I needed! To a tee! And it's perfectly clear and logical. I can run with this now! I got so excited FM Forums and my system locked and I had to 3-finger-salute! I want to stay up and do this right now but I don't dare. You tickle me! Hours and hours I've spent trying to figure how to keep Users out of my process! They have lists they want to bring in NOW!!

:thankyou: