related record in a different database

[Ben Ball]

I have 2 databases...

 

one looks at the other through various relationships...

 

I can't seem to goto related record if its in another database... the function does'nt seem to work...

 

what can I do about this? or, really... how can I go to a related record in a "different" database?

 

tia

[Ocean West]

your script should be ---

GO TO RELATED 

PERFORM SCRIPT ( SCRIPT IN RELATED FILE ) 

 

this script will target the layout and perform any other items you need to do.

[Ben Ball]

Thanks for the response.. but I am not sure I quite get it..? I have attached a very basic example (2 databases) and my attempt to sort the issue..

 

"test" looks at "test copy", the script starts from "test"

 

Please advise...

 

TIA

[LaRetta]

No file attached.

[Lee Smith]

I have attached a very basic example If you tried to attach a file, it didn't make it. The process is rather easy, if you follow these steps.

Step 1

Find the file on your hard drive, and zip it.

Step 2

Do not use the Quick Reply, BUT instead, choose the More Reply Option 

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Select the file, and then click Attach This File, and then click Add Reply.

If you should have a problem let us know.

[Ben Ball]

for some reason.. I can't upload it....

 

I keep getting a message... " No file was selected for upload"? I can't even upload a screen grab of the error...

 

TIA

 

 

[doughemi]

The administrator is investigating this:  http://fmforums.com/forum/topic/86818-unable-to-attach/

[Ben Ball]

finally can upload it... can anyone help....?

test Copy.zip

[LaRetta]

You have given us nothing from which to work, Ben.  Are you sure this is the file you meant to upload?  What is file Test and TestCopy?  One single field called goto1 in each table?  No names, no descriptions, no nothing.

 

What are the names of your two tables (or files) and how are the two related?  This is obviously not your file - you say GTRR fails but we do not know why because you have given us nothing from which to even make a wild guess.  How can you possibly expect us to answer you?

 

Why are you using two files?  In FileMaker, we refer to files and multiple tables within a single file.  You rate yourself intermediate.  What do you want?

[Ben Ball]

Hi, apologies if it is unclear..

 

I just wanted to keep the files simple. I am just trying to go the related record in the test copy database. I tried the solution by ocean west.. which I could'nt make work.

 

I attached 2 files.

1 "test"

2 "test copy"

 

if you look at the relationship in test 1. You will see that it views the table and records in the "test copy" database.

 

the goto 1 field in "test" is related to the goto field in "test copy"

 

I have put a script in "test" called "new script" that is attached to the related field (test 2:: goto1) in test copy.

 

Basically... I was wanting to go to the related record/layout, in the related database test copy.

 

So my original question is still the same... just tried to supply something very simple.. perhaps too simple?

 

the reason I want to do this is because I am having to disect an exisiting solution which is getting too big. I am seperating tables into different sub-databases that will all need to access the "main" database... not only view records.. but I will need to go into the related records in that "main" database.

 

I noticed that if the "test copy" database is closed and you click the "test 2:: goto1" (right hand "yes") field in the "test" file then it will actually work... but if both files are open... it does'nt work...

[LaRetta]

You do indeed have an external source established to your second file (test copy).  But since you already have an established external data source and table occurrence for 'test copy' then why go to the other file at all?

 

The other consideration is that you do not have a layout in Table Test file based upon test copy.  Oh I wish you would please give me real names to call these tables.  I have no logic from which to work.  Are you wanting to go to the related records in Test Copy and work there?  Why?  Are you wanting to work in this file (which is suggested) instead?  What do the records in either table represent?

 

Even with separation model, it is RARE to require working in another file since you can handle everything in a single file.  I am afraid that, without clearer direction on what you want to do, I cannot answer.  Maybe some folks can work totally abstract but I am not one of them.  Please provide more information.

 

Also, are you still on version 8?  If you really need to go to the other file, I believe there are windowing behaviors that changed which we might need to address.  :^)

Edited November 28, 2012 by LaRetta

[Ben Ball]

Thanks for looking into it.

 

My situation is somewhat more complicated than what I am asking for. 

 

Basically, I have a huge database (Management.Information.System) system that runs quotation, orders, planning, inventory, production,delivery and invoicing. We have bought another company which essentially is exactly the same as our business. Except it is in a different geographic location.

The current system needs to be adapted to manage jobs in both sites via the internet. At present the 2nd company uses a virtual server at the 1st company to access the records in filemaker.

 

This is obviously very slow and frustrating.

 

I have created a seperate planning database, that will essentially look into the 2 companies and plan jobs from the 1st company. I will need to view related records in both databases in order to see the information I need. Without having to re-create relations/layouts etc. 

 

Obviously the system was'nt designed to manage 2 companies in the first place. So I am trying to adapt it as best I can.

 

The other issue is that the 1st company runs 24 hours a day and down time on the 1st database is just not possible for the amount of work that is needed.

 

I guess the more solid way is to just re-create/duplicate layouts that look into the 1st company database, rather than trying to "go to" related records in a different database...

 

I think you have answered my question.. by saying, its not a usual practice.... I am just trying to save on my work load.

[LaRetta]

Hi Ben,

You CAN go to another file via GTRR. And I can show you how but again I would need to know your FM version and a few simple field names to get perspective of what you want to achieve, ie, Customers in Test and I want to go to that Customer's invoices in Test Copy etc... However, if Test is one business and Test Copy is another, why would you ever GTRR between them anyway?

Why not migrate the data instead into one business as it has now become ... one table with Customers, one table with invoices etc. I doubt it would take more effort and it will make things much simpler. You do not want to spend your time searching two files for a single customer or having to combine financials for reporting ... It would require temp table.

I assumed there was more complexity here than you were saying and there may be good reasons for your choices but migration seems the logical direction from my perspective. And if one company works 24/7 then I would highly suggest separation of data completely (a UI file and a Data file) so you can safely make changes in the UI.

[brian rich]

Hi Ben

 

You've got two records in your TESTdatabase; both have the value of goto1 as 'yes'.  Change one of these to 'no'

 

You've got twelve records in your TEST COPY database; eleven on these have the value 'no', one has the value 'yes'.  Change the view in this database to table view, so you can see the found set.

 

The first line of 'New Script' in your TEST database does work - it will select the related records in TEST COPY.  Your second script line doesn't really make sense to me. Delete that script step and replace it with a Select Window "Test Copy" with the current file box unchecked.

 

If you now run the script from the test database, you will end up with the TEST COPY database window at the front, with the related records selected, which I think is what you were expecting.

 

On the subect of remote access, you're always going to get some performance hit if you're going via the internet. Using a terminal services approach will minimise this, but you'll need to make sure that your ISP provides a reliable service with acceptable upload and download rates

 

HTH

 

Brian

[Ben Ball]

Hi La retta & Brian,

 

Thanks for your time. Ok, I have attached another 2 files... which now works.. I can go back and forth from file to file in the related records/table.

 

I do feel this could be a nightmare doing this... But it will be necessary in the interim.

 

La retta, I am on filemaker 11 now. But fairly new to it. What is a UI file and a data file? (or the relationship between the two? Please advise.

 

Brian, Yes, we are using a terminal server.

 

Both La Retta & Brian, For example. With the system, I kind of want to be able to say work on my pc in my office...

with a database on my PC which relates only to the tasks I am doing. Stand alone. The jobs I pick will be shown from the main terminal server.

 

I need to post information into a central database (on the terminal server) with related records when I am finished. I am trying to make it so that when I am working on my pc it is as fast as possible and only need to access the main database when I am finished... I will run a script that posts the info I need to and then see what my next job is. "grab" the info I need into my stand alone database, work on it.... and then, again, post it back in when I am finished.

 

What is the most solid and fail safe way of doing this? The "posting" and "grabbing" that is. Also, need to be sure that nothing happens until the information is grabbed or posted correctly....

 

we are getting deep now....

 

see.. my main worry is that if concection fails between the terminal server and my pc... half way through a script.. how do I know it has failed...?

 

Ben

test 2.zip

[LaRetta]

Hi Ben,

 

What is a UI file and a data file? (or the relationship between the two?

 

UI is the User Interface where you design in layouts, scripting, reports, all graph (relational) work ... probably 90% of your work time once the original tables are created and data migrated and the kinks are worked out.  The Data file is where the data tables reside holding the data and the calculations (which 90% of the time are auto-enter from base entity TOG contrived in the Data file).

 

By using separation, ah heck, I won't waste time repeating - I said it here http://fmforums.com/forum/topic/82917-creating-a-separation-model-from-an-existing-solution/

 

The jobs I pick will be shown from the main terminal server.

 

I need to post information into a central database (on the terminal server)

 

This is a bit concerning, Ben.  FM should not be on network server.  Do I misunderstand this?

 

I am trying to make it so that when I am working on my pc it is as fast as possible and only need to access the main database when I am finished... I will run a script that posts the info I need to and then see what my next job is. "grab" the info I need into my stand alone database, work on it.... and then, again, post it back in when I am finished.

 

Safest way?  Don't.     Every time you mess with data you increase risk.  Have you first exhausted all possibilities of 1) make your system uber-fast?, 2) use FMS on dedicated server?, 3) consider what will be involved in your scripting to make this happen?  Every time you add a field, you will need to remember to map it (I assume you would be using import/export scripts).  I am not saying don't do it (store part of your data locally), just be aware of the risk and consider alternatives first.  Why not work directly on your data?  How much data are we talking about?  If large volumes downloaded from server at a time (number of records) then maybe yes.

 

If FMGo and large volumes, I would suggest using a plug-in such as MirrorSynch from 360Works.  I would probably include recommendations for other FM synch solutions but that is the only one I'm familiar with, hopefully others can suggest as well. This isn't always necessary - I have too little information to recommend, sorry. There are many configurations and none is absolutely right in all situations.

 

see.. my main worry is that if concection fails between the terminal server and my pc... half way through a script.. how do I know it has failed...?

 

A valid concern if you truly mean a terminal server and not properly configured, dedicated FM Server.  But accessing a proper setup with FileMaker Server, is the safest and what I would always recommend and learn all tricks to keep your system fast.  This (loading data onto User) is one of those tricks but it can be more difficult to implement and administer which is why MirrorSynch and such exist.

 

My big concern for you is loading FM onto terminal server.

[Ben Ball]

Hi LaRetta,

 

Thats very helpful info. Thanks.

 

I am running FM server 11 on a virtual dedicated server (terminal server). There are 30 people working on this system at any one time.

 

I'm not actually importing and exporting data as such.

 

Basically,

1. We create a quotation with design information.

2. We get an order & Log the order details, the quotation details, all relevant field information gets "posted" into the "production sheet" using a series of SetField functions from table/field to corresponding table/field.

 

All this in the main database.

 

Then, this information in the production sheet/record is standard and only gets viewed via related records.

 

consider below.. "test" file is on FM server on a virtual server at work. "test copy" file is at home. 20 miles away. Bearing in mind.. I dont have all these fileds in the databases that I attached. I am referring to "test" and "test copy" just for a reference.

 

3. Production starts."test copy" file I see the production portal  from ("test") with my list of jobs. I click on the relevant portal row with a script that will run and set fields into my new record. (All this is information transfer is for traceability purposes). I enter data into a series of fields in "test copy". Once complete I then run a script that sets fields with all the latest production information into the "test".

 

The main point is that I must set fields in the main "test" database that will guide that record through the system.

 

I get the seperation idea where calcs are kept in the main database. This would certainly speed operations up.

 

Is there some script step? I can run at the start of my scripts that will check for connection to the "test" or "Test copy" database? That way, if it fails I can halt the script and send a message.

 

I also think there is now a better/quicker way to set fields from table/record to table/record? Just can't remember how?

[LaRetta]

"2. We get an order & Log the order details, the quotation details, all relevant field information gets "posted" into the "production sheet" using a series of SetField functions from table/field to corresponding table/field."

You should create the data once and then use the record's primary key only to set the other table. I am not sure what to suggest without seeing your file (your workflow feels a bit off but may just be perception from quick overview). There is no reason that all Users including yourself can't work on the data at same time. If you are working on parent while Users are working on children then you might get record locking but that isn't a dirty word; it is a blessing. If the figures truly depend upon complete child group then you might wish to research transactional methods such as presented by Todd Geist (sorry not in position to look up link for you).

[Ben Ball]

Thanks LaRetta... The workflow actually works very well in the system.... Its huge, its complex, comprehensive and took me years. The solution is 0.5Gb so its kind of too large to post...  Really... only a certain department will work on a record at anyone time.. so the record locking is'nt much of a problem...

 

I will research Todd Geists work.... thanks

[LaRetta]

If record locking isn't an issue then you do not need transactional model and I doubt there is good reason to store data on your desktop. :-).