Do not want to use rel calc fields

[EddyB]

Hi all,

Has anyone any ideas how to do this one.

I have 2 databases.

DB1 is addresses

DB2 is personalDetails

The user is working in DB2

They receive a custom dialog asking them to enter PostCode and House number.

This sets the PostCode field to whatever and the house number to whatever

I then have a calc field which joins the 2 as follows:

1, M227LG

DB1 contains or will contain all UK addresses

This database contains the same field as above

At the moment I have this set up so that once the user has entered the PostCode and House number the rest of the UK address is filled in using a relationship to DB1.

If the Postcode and house number is not found in DB1 they receive a message saying Address not found on database.

Now the problem I have is if the database does not contain the address they need, then they need to be able to manually type in the clients address in DB2.

At the moment, as the rest of the address is using calc fields for the relationship, the user cannot type into them to manually enter the details.

How can I transfer the address from DB1 to DB2 when the user enters the PostCode and House number without using these calc fields and without a long script copying from DB1, pasting into DB2, copying from DB1, pasting into DB2 and so on.

Any suggestion please?

Many thanks

Ed.

[ernst]

Hi Ed,

Doing this via copy-paste would indeed be cumbersome.

I would personally do it as follows:

-make two global fields for postalcode and housenumber

-when the address is not found and thus the related record in db1 does not exist use the 'set' command to put the values of postalcode and housenumber in the global fields.

-make a script in db1 that makes a new db1 record and sets the postalcode and housenumber field with the values from the global fields

-have an extra custom dialog that lets the user fill in the missing detail(s). Because the previous steps already made the relation to db1 valid, you can have the custom dialog directly act on the related fields.

Hope this helps,

regards,

Ernst.

[Ugo DI LUCA]

Hi Ed,

At the moment I have this set up so that once the user has entered the PostCode and House number the rest of the UK address is filled in using a relationship to DB1.

Off topic : What is that House Number you have in UK that can guarantee uniqueness. I've never heard of this ?

Anyway, as suggested by Ernst, the global field is surely the best way to go when you're creating new records in a db. For example, associated with a script, these globals could become very powerful, controling any possible dupes and display a list from which you could choose the correct id.

Now the script could look something like

If isValid(your GlobalRel)

Create New record

Setfield (your key, GlobalConcanate)

Setfield (your field1, ConcanateRelationship::field 1)

Setfield (your field2, ConcanateRelationship::field 2)

...(other setfield(s)

Else

Perform External Script

-----------> Create New record

------------ SetField (global in Adresses, record_id)

Create New Record

SetField (TemporaryKey, ConstantRel::global in Adresses)

Go to Layout

Go to field (::TemporaryRel:Postal Code)*

where :

- GlobalConcanate = Global 1 & ","& Global 2 (structure equal to ID in db1)

- GlobalRel ---> PersonalDetail:GlobalConcanate::Adresses:Your key

- TemporaryKey is of same format of your Record_Id in the Adress db

- TemporaryRel--> PersonalDetail:TemporaryKey::Adresses:Record_Id

- ConstantRel ----> PersonalDetail:g_constant (global=1)::Adresses:c_constant (calc=1, indexed)

* you need related field on the new layout when the Validation script statued that there was no matching records in Db1.

[EddyB]

Hi both,

Thanks for your replies.

I think I may have been unclear about what I need to do.

If requested data exists in DB1 then this info needs to be transferred over to DB2

If requested data does not exist then the user needs to be able to type in the address into DB2.

I do not want the address to be added to the master set of addresses in DB1 I will explain why shortly...

Also, I cannot use a relationship to grab the data from DB1 to DB2, I need the data to be permanantly stored in DB2.

The reason for this and the reason for not adding a new record in DB1 is, the data in DB1 is data that I have to purchase - a full list of UK addresses.

I will recive an update to this data every 12 weeks.

So firstly, if I create a new address in DB1, it will only be over-written when I do the update to the data.

The reason I cannot use a relationship is, the records in DB2 will be stored for 10 years. Now imagine you are a client and I get your address from DB1 and through the relationship it shows in DB2.

5 years from now, your house may no longer exist, the updated data therefore will no longer contain that address, when I update the data and it rids the DB1 database of your address, your record in DB2 will no longer have an address.

I need DB2 to store the address even though it has been removed from the main data in DB1

Does that make sense?:

Ugo - your question about house numbers.

Each road in the UK has a post code and each house on that road has a number.

So for example if you lived on "Bank Road" your post code would be M1 7QN

If you lived at house number 1 on Bank Road, your details would be 1, M1 7QN

Your full address would be:

1 Bank Road

Salford

Manchester

M1 7QN

(this is a fake address by the way, please do not send my birthday cards to that address!!)

Many thanks

and sorry for the confusion on my previous post.

Ed.

[Ugo DI LUCA]

EddyB said:

If requested data does not exist then the user needs to be able to type in the address into DB2.

I do not want the address to be added to the master set of addresses in DB1

Also, I cannot use a relationship to grab the data from DB1 to DB2, I need the data to be permanantly stored in DB2.

Just use a lookup field then instead of your calculation !!? Set your lookup to let the field empty if not found. If you want to lock the lookup, you can...

My question about house numbers.

Each road in the UK has a post code and each house on that road has a number.

There's no uniqueness here ! What if I live in a 7 floor building with 40 appartments. Would you invoice my neighboor !

[EddyB]

Aaahhhh! that is slightly different!

If you live in an apartment, you will have an apartment number as well as a building (house) number

So you would live at:

Apartment 71

1 Bank Street

Salford

Manchester

M1 7QN

Actually, now you've mentioned it, that does change things

Oh dear....

I have seen software using this data before and what happened there is, you enter the post code,

If the post code had 10 or less addresses, it displays the 10 addresses and you select the one you want

If the post code had anymore than this it would give you categories in multiples of 10 for you to choose, ytou choose a range and it shows those 10 addresses for you to choose from.

So for example, if your post code had 44 addresses attached to it, you would enter the post code in.

The software returns:

1-10 Bank Road

11-20 Bank Road

21-30 Bank Road

31-40 Bank Road

41-44 Bank Road

You click the range for the house number

It brings up the 10 addresses and you select the 1 you require

If number 1 was an apartment block contaning 10 apartments, when you clicked on 1-10 Bank Street it would return

Apartment 1, 1 Bank Street

Apartment 2, 1 Bank Street

Apartment ...(and so on), 1 Bank Street

2 Bank Street

3 Bank Street

etc

I had absolutely no idea how to recreate this in FM, if you had any suggestions I would very grateful!

Thanks again Ugo!

Ed.

[Ugo DI LUCA]

Hi Ed,

This is easy to implement with FM. It is the "check dupes" script step I mentionned above that brings you to a layout with a portal from which to choose from.

The script would have to :

If Is Valid(your rel)

If (Count(your rel::record_id)>1

Show layout with portal to choose from.

I've done a sample some time ago called "check duplicates" in the Sample section (about a year). Pick up the first demo as all others aren't working properly (I should have deleted them BTW).

Just give you a list of all matching records to choose from.

In your case, I think it can apply...

[ernst]

Hi Ed,

Still think it's doable,

Solution could be (if I understood correctly what you want to do)

-use globals for postalcode and housenumber and a calculated join of both fields in DB2

-use these to show the matching address(es) from DB1 in a portal or so...

-each address in DB1 should have an unique ID field that gets copied to DB2 when the user selects an address.

-a relation between the unique fields could then be used to lookup the data from DB1 in DB2.

-if no address exists the user types the data in the directly into DB2, the lookup just does not take place then.

Am I missing something completely here?

regards,

Ernst.

[Ugo DI LUCA]

100% agreed.

That's what I'm suggesting.

A portal for all related match to choose from.

I may be confusing tonight. Sorry.

[McCormick]

Another thought on these:

What if you created more files? Perhaps this is not worth it, but I am thinking of something like this:

DB2: personal details

DB1: addresses that you purchase, replaced regularly

DB3: addresses that you add manually as you encounter them (a private list)

DB4: pulls addresses from both DB1 and DB3, so it always presents a complete and up-to-date list of addresses to check.

Replacing all of DB1 won't cause any problems

New info can be entered into DB3

All searches can be performed on DB4

As far as the data getting old, that is always a danger, and I would suggest modification dates as part of the solution, wether adding new files or not.

[EddyB]

Many thanks for everyones helps on this. I think it's the portals I was missing, Ugo has given me some example files so I think I should be able to do this now.

McCormick, thank you for your suggestion. In a way I will be doing this but a different way around. Once an address is added to DB2 (if it did not appear in DB1) we will add it to a client database with the clients permission.

We have to be very careful in the UK with keeping addresses due to our Data Protection Act. I will be covered with the purchase of the data, but any extra data I collect I will need permission to keep so I am just adding a button, if they agree I will add this address to the seperate database for future reference.

The portals suggestion will allow me to take a huge amount of addresses on 1 post code (some post codes cover a huge number of houses) and put them into ranges, I can then select the range, then select the exact address from that range.

Many thanks again for everyones help on this

Much appreciated!

Ed.