Hi, I am the manager of a cemetery. We have four databases running in QA4, that I would like to import, and integrate into Sesame, so that I can pull records from each into one form.
I have no difficulty in converting the databases into sesame format, but I'm lost when it comes to linking them up, and the best way to go about it.
The basic way it's layed out now,
The Lot register contains the owners contact information, and notes on general lot info. This I would like to be the main area, from wich, I can call the others, is that the best way to do it, or should I design a new Database, and use all the former Q&A databases as sub databases?
The others, are the Burial register, with over 15000 entries each giving the genealogical information of a burial.
The Flower database, giving contact information for whoever is looking after the flowerbed, if any for the lot, and used for mail merge operations. (May or may not be the same as the lot owner info.
The Pre-need database, that gives information on services that have been prepaid for that lot.
The winter wreath database, much the same as the flower database, also used for mail merge 

What I'd like to be able to do, is search the main database by lot owners name, and pull up from that any information i need about the lot from the other databases.
For example, I'd like to be able to ask for, and get, a list of all the burials in the lot, and the grave numbers they are in.
When this was attempted in QA4, it would retrieve the first burial it came to, and stop. This was not acceptable, and we never did find a way around it. We ended up using each database separately, a major PIA.
I have been working with sesame off and on for some months now, but I can't figure out how to integrate these databases the best way.
In fact, I can't figure how to get an existing database to be a sub database.

John,

How about starting slow and simple to get some solid experience with Sesame and then figure out how to deal with your entire project.

Start by making a new application
Open Sdesigner
Click new application
In the filename box type CEM
Click on forms
Click design a form
Click add new database
Create new text element named LotOwnerName
Click save layout design
Click close design tab
Click add new database
Add new element LotNumber
Click save layout design
Click close design tab
Click redesign a form
Select form newdatabase
Click form named newform
In element adder click select element type subform
In the label box enter subformlot (or anything you like to call it)
Click add element to form
A new box will pop up step 1 will be select a form
click the down arrow and select NewForm0
leave step 2 and 3 alone
step 4 type a name
in step 5 change form to table using down arrow
click ok and place and size the new subform element on your form
Click save layout design

Click close design tab

Now you can open the application named CEM enter a lot owner name
And enter lot numbers relating to that owner.

When you later search by lot owner all the associated lot numbers will show.

You can add as many fields to the subform as you need and you can add additional subform elements to display whatever you need.

This example should give you an understanding of how to progress.

You can definitely make your form prettier and name things nicer and improve this but this should show you the basic principles involved.

Hope this helps.

If you are talking about adding elements to the subform just open the application in Sdesigner and click forms then redesign customize a form and choose the database form you want to add elements to. 


If you are talking about adding data to the subforms from the parent record just enter it in the field. Press f10 to enter another. Sesame handles the stuff that links the child data to the parent for you in the example I used.

John,

I have the example above application working on my machine.

If I add an owner name of smith to the main record
Then click in subform and enter lot 69
Press f10 and enter 70

Then go back to main enter another new owner jones
Then click in subform and enter lot 150
Press f10 and enter 160

Exit database

When I go to the main database and search for jones it brings up 150 and 160 in the database.

Am I not understanding what you are wanting to do?

Ok, I just went through and created the database exactly as you described, and it worked perfectly.
The trouble is, it looks exacltly like the one I made with the data already in the databases, except that mine does not work.
Is there a way I can compare the two side by side to see my mistakes?

Quote:


Yes, i'm afraid that's the way It has to be done, I have five fairly large databases that I have translated fron QA4, and I want to be able to get at the data from each from one form.

Quote:

Bob, I suspect that he should set the subform relational and specify the key fields - temporarily. Then once he has imported his data under the proper parent, he should set it back to natural linking. That way he avoids the ongoing "housekeeping".

This isn't something that happens automatically by changing the link type. It is a process you can do. Several Forum members have done it and say it works quite well. It works like this:
1. Create the relational link
2. Do a hierarchical export of the data.
3. Delete the relational link
4. Create a natural link
5. Do a hierarchical import of the data.

John,
In Q&A you have (for simplicity's sake) two flat databases. Lets call them database A and database B. The records in database B, are "related" as children to the records in database A. In Q&A, that "relationship" is caused by an XLookup statements in Q&A Basic that find some of the records in database B, while you are looking at a single record in database A. This is done (usually) by field matching. In other words, there is a field in A that has a value that matches a particular field in B. In Q&A this doesn't really move the record from B to under the record in A - it can just retrieve information from the record in B - because Q&A is entirely flat.

In Sesame, you can have subform and sub-databases. So you translate A and B into Sesame - you end up with two flat databases and two flat forms. You can then merge B into A, creating a single application file containing both A and B, both still flat. So you create a subform field on A and tell it to contain records from B.

All well and good, and from what I've read so far on this thread, this is where you are now. But there still has to be a way for the A form to know which B records should appear under which A records. In Sesame there are two ways for this to happen: "natural linking" and "relational linking".

In natural linking sub-records appear under a parent record because that is where they were created in the first place - there is no need for matching "key" fields or anything else - it got made here, so here it stays. Natural linking can't immediately help you because your records have already been created elsewhere.

In relational linking (an option available when you created the subform field on the parent form) you tell Sesame which field on the parent and which field on the child need to match for a record from B to appear under a record in A. Sesame then dynamically finds and displays all of the correct B records each time you view an A record. It does this by running through the B database looking for those B records that have the same value as the specified field in the current A record.

You probably don't want to use relational linking permanently because it is slower and does require maintenance. But you can set up your system to use it once - to find the right subrecords, then using the ASCII export save that relationship. Then you can rebuild your subform field with natural linking and import the data back in.

1. Translate A.
2. Translate B.
3. Load A into Sesame.
4. Merge B into A.
5. In SDesigner, Create a subform field on A's form that contains B's form.
6. Tell SDesigner that you want it to be a relational subform and tell SDesigner which fields need to match (one field on the parent form and one field on the subform).
7. In runtime, using the export spec, export all of the fields on the parent and child to a file.
8. In Runtime delete the records from the child form and from the parent form, using mass delete.
9. In Sdesigner remove the subform field from the parent form. Then, recreate it just like you made it before, but this time select "natural linking" (the default), instead of relational.
10. In runtime, do an ASCII import from the file you created when doing the export (step 7).

When step 10 is completed, you will have the correct child records under the correct parent - naturally linked.

I know that I am being more conceptual than specific, but once you understand what is going on, the specific steps get a lot easier. The most important thing is understanding that you need to tell Sesame some way to determine which subrecords appear under which parent. In you case that may be a "lot number" field that needs to match on the parent and the child.

The Cow
I tried to export, but could only export from the form, the subform still contains no data, and thus there is nothing to export. ???

Quote:

That takes us back to steps 5 & 6
Quote:

If, after you complete step 6, you go into runtime and do a search on the main form (A), and you do not have the subrecords you expect under the main form - something went wrong in the relational linking. Check your key values in the main form and in the subforms and make sure that they do indeed match. Use Sesame's search capabilities to test for this. In any case, do not proceed to the export until you can see the correct subrecords under the parent form in runtime.
If everything looks right, but you still don't see your subrecords showing up, shoot an email to support@lantica.com. Ray will get it and either find and fix the problem or forward it to me.

Bob
The amount of programming, particularly in my flowersheet , and endowment databases is massive. Manually re-entering it would take me weeks of dedicated hammering with my two fingers.

Well, it's not me...
I just went through the proccess without using any qa4 data, and everything works fine. It's something to do with the fact that the data came from two separate qa4 databases.

Quote:


Hello John,

We would simply use your DB file to better explain how to do this and to help guide you, not to actually do it for you.

-Ray

Fake data is perfect, just as long as it is in the same format as the real data.

-Ray

Well, Ray has sorted it out, now I'm waiting for an email from him as to how he did it. Looks great. Many Thanks 

Thanks again, just one more question..... I hope 

Can I design the database, get everything set up, then, once the student has completed the 'code' field entries, import the data into my newly designed database?

My plan to do this, would be to convert the data from Qa4, then have the student do the work. (becomming familiar with sesame in the process). Then, import the data to my design.

Can you see any issues, or problems?

Sounds simple, hope it turn out that way, thanks.

What is the full line of code?

You can narrow it down to the problem line if split that one long line onto 8 seperate lines. ex

If selection = "1" then fieldname1 = fieldname2; If selection...

becomes

If selection = "1" then fieldname1 = fieldname2;
If selection... 
etc.
etc.

This will give you a better idea where the problem is.

Breaking it down line by line, shows the problem to be in the last line, selection '6'

Thanks, that worked beautifully.

To delete an unwanted report or form in Sesame, you would use the Layout Manager which is covered on pages 119-121 of the Sesame User Guide.

-Ray

You might want to consider renaming the reports and not delete them until they have all been imported in case they are not duplicates of each other, but only share a common name. 

Might not be necessary in these cases, you know your own databases, I am just conservative by nature, and deleting is always a last step after checks and rechecks.....

Next newbie question 

I'm having some problems with fields. I have merged the databases, and created new 'code' fields in each. When I go to create the subform, the 'code' fields have vanished.
If i go out of the merged database, and check the forms directly, the 'code' field is still there.

I may have corrected a second problem, and that was that I was attempting to rename a field that  I later found out was mentioned in programming. That wouldn't fly either.

Sorry, I am refering to layout elements (that's qa4 sneaking in).
The 'code' layout element was added to each (terminology again) form, so that I could use those LEs to link the forms using subforms  to a master database.

Incidentally, my attempt to rename the LE also failed even after I changed the programming statement.
it appears in designer that i have made the change. I then save, close, and reconcile, and nothing happens. When I reopen the design element, the name has not changed. Doesn't appear in preview either