Community
Showing results for 
Search instead for 
Do you mean 
Reply

Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

New Member
Posts: 31
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

They greyed-out default field did become ungreyed-out post-reschema. Although setting default to UK made no difference to the error. It has reverted back to greyed-out since following your subsequent instructions. Cannot see any way of setting default UK to phone/mobile fields.

 

Yes, on My Record both phone & mobile are set to UK as is Country.

 

This error happened after changing some contact fields from character to initial-caps (address, city, county) - please see initial post for full details.

 

This is causing issues as if we forget to set the country phone/mobile code for a new contract (using grey box next to field), the entire contact is not saved and is lost!

Moderator
Posts: 450
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

I will check with my senior team regarding these issues. my databases have the Country field has the default value greyed out, I have seen in the past with changing system fields even being a small change can cause issues to the structure and way it will work.

Moderator
Posts: 450
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Having spoken with my senior team this morning. It is actually a defect that the default value is greyed out and is still presently open.

There was an issue that appeared in a previous build of Act that stopped the default value working and the work around was to grey it out and keep the defect open.

 

In changing system fields to 'Initial caps' this will actually break the database and cause the error. I have been advised that it can be fixed by changing the fields back to 'Character' and then preforming the reschema (This will hopefully avoid the need for an empty copy)

 

Regarding the phone code not correctly defaulting, this could be cause by and incorrect call list. with Act closed you can go to Control Panel > Phone and Modem > it should ask you to create a new location. ensure that this has the correct country, if you already have one please recreate it via the 'New' button and set as default by the radio button. Once this is set open Act! running as Administrator and see if it is defaulting correctly to UK.

New Member
Posts: 31
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

I a;ready had a correct UK phone/modem set up. Did try adding a new one but that made no difference. Edited original and re-tired to no avail.

 

So to recap, issues  with database are:

 

  1. My Record Country is UK but default phone & mobile country code is now US. This is the real pain which needs fixing.
  2. Greyed out default country field (in Tools > Define fields> Country > Default Value (optional). Not sure if sorting it has any benefit as it didn't fix (1) last time.
  3. Showing as non-local even though it's on the PC. Guess this need sorting in the long-term to enable new databases to be created from the original.

Thanks for bearing with me on this - hope you can suggest a remedy.

 

Moderator
Posts: 450
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

No a problem I hope we are getting closer to resolving your issues.

 

1. Just to be certain are you creating a new contact to check if the country code is corrected as it will not change for contacts that are already set.

2. This issue (Country Default value greyed out) is a presently open defect which will hopefully be resolved in a future build soon.

3. Just to certain have you changed the Character fields that were made into initial caps back to character and then preformed a reschema? i would also like to try unsharing the database (Tools > Database maintenance > Share database make sure this is unticked) and try again to get the error.

 

New Member
Posts: 31
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Answers:

 

1. Just to be certain are you creating a new contact to check if the country code is corrected as it will not change for contacts that are already set.

Yes - existing contacts are all OK. It's just when creating new contacts that I have to manually set phone/mobile country code to UK (I don't normally bother inputting Country filed as most are UK businesses.

If I do insert UK into Country filed, that does make phone/mobile correct. However, never had to do that before issue started.

 

2. This issue (Country Default value greyed out) is a presently open defect which will hopefully be resolved in a future build soon.

I was told that by ACT support. Just tried it to overcome (1).

 

3. Just to certain have you changed the Character fields that were made into initial caps back to character and then preformed a reschema?

Yes, I did revert to Character. I did try reschema twice following your instructions but that didn't work. Just tried reschema again - it didn't fix (1) but I no longer have the non-local database when file>save copy as... one issue sorted!

i would also like to try unsharing the database (Tools > Database maintenance > Share database make sure this is unticked) and try again to get the error.

It wasn't ticked for sharing. To be certain, I did share & unsure (unticked). Error still there.

 

So, back down to the main error I first posted - incorrect phone/mobile country code following changing fields from character to initial-caps.

 

 

Highlighted
Bronze Elite Contributor
Posts: 1,395
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

[ Edited ]

I have recreated the error in a demo database -

 

If the Country field is changed to Initial Capital ACT can no longer run the script that looks at the logged on user's Country as it expects a Character Field.

 

Changing back to Character field does not fix the error and rebuilding the schema also does not put this right.

 

ACT now will default to country which is 1 = USA  

 

ACT design Team - Please look at locking field format for this system field.

 

Will take a look in SQL Studio but do not hold out much hope of fixing.

 

 

Moderator
Posts: 450
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

I believe the only way we will be able to fix this issue at this time is to do a Back up > Restore As the database to create an empty copy and then import the data. doing it this way will prevent the issue the from coming across since it should create the fields that were changed to 'Initial Caps' as new.

 

As this issue is logged as a defect I would hope something would be put in place to prevent this from happening.

Bronze Elite Contributor
Posts: 1,395
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

Slightly different suggestion.

 

Create a new database, add any custom fields you have in the damaged database.

 

Add users.

 

Import from damaged database using ACTs import wizard. As long as field types and field length are the same (No Initial Caps please !!) then ACT should correctly pull all the data across.

New Member
Posts: 31
Country: United_Kingdom

Re: Error: The INSERT statement conflicted with the FOREIGN KEY constraint PHONE_PHONEMASKID_FK

[ Edited ]

Hi Horn

 

Thanks for the response.

 

I believe the only way we will be able to fix this issue at this time is to do a Back up > Restore As the database to create an empty copy and then import the data. doing it this way will prevent the issue the from coming across since it should create the fields that were changed to 'Initial Caps' as new.

 

Sadly that didn't work. Created the empty database as per your instructions and tested making a new contact - still wrong default of US.