Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IPOCC inconsistency in Customers DB

Status
Not open for further replies.

LegoTajkun

Technical User
Feb 15, 2017
94
HR
Hi.
Facing issue in Customers database (IPOCC R10.1) – in csv file that is used to import customers particular/problematic number for customer exists, during import system did not return any error/warning. But in case call from particular number (entry exists at csv file) customer name is not matched/displayed. Checking (UI Administration) this particular number and customer name in Customers list – this particular tel number missing (all other contact info are there). Try to add number system returns that this number is already in use (exists). But when make search for this number non entry was find/displayed. Strange. So, turn to pgAdmin and check with query – again strange outcome:
select * from kontraaddress where address = '0043461020'
entry is listed (with kontraid),
tmp1_bjzznp.png

BUT when search for this kontraid non dana appears:
select * from kontra where kontraid = 'e99f545ad1019f04'
tmp2_ntkhn7.png

Seems that numer exist but it somehow not connected to table „kontra“
If I did not miss conclude problem how this can be corrected/fixed (just to remove this particular number from DB)?
Any similar stories?
Cheers!
 
It works for me and I have data i both tables.

- How what does your import files look like?
- Does the CLIs really start with double zero?
- Can you see any entries in the Customers tab?

"Trying is the first step to failure..." - Homer
 
It is more than 15K entries. CLIs for all entries are in same format (all start with double zero). Up to now this one was firs noticed case. For other entries / CLIs works fine (which are called). Checking the csv file, Customers list/numbers and DB noticed that "missconnection".
 
Does it have any strange characters in it's name that might caused the import to fail?

"Trying is the first step to failure..." - Homer
 
Hi janni78, thanks a lot for hints.
No strange characters. I upload same csv file to test IPOCC and problem was not reproduced. Queries form UI and SQL are fine.
DB1_OK_deaefs.jpg

DB2_OK_b4qwd8.jpg


Since cvs file was changed many times in past possibility is that the problem was caused by some previous csv file version but detected (as particular CLI called) now.

Way to go is find hot could clear/remove particular CLI (kontraaddress) form DB not making bigger mess? Any hint for deleting entry form DB.

I try clear DB through by UI Admin Import with existing data cleaning - but did not help (all contacts data are deleted but problematic number stayed). Assume that reason is that for this problematic/corrupted entry 'kotraid' is not listed in 'kontra' table.
 
There is a trigger on public.kontra that deletes the same ID from public.kontraaddress and public.kontratargets when it gets deleted.
Since you don't have that ID in public.kontra that is probably you issue.

You should be able to shutdown IPOCC and manually delete all entries from the tables, and the import them again.

Just do a backup =)


"Trying is the first step to failure..." - Homer
 
Fixed, hope without side effects :).
Number is deleted with command at SQL:

[tt]update kontraaddress set address = '' where kontraid = 'e99f545ad1019f04'[/tt]

Then manual adding for this CLI was possible.
Also manual import of csv file passed and number is listed as expected.
 
Hi janni78, sure backup first. Fixed by update of particular entry since (for now) it is only problematic entry. Thus shutdown IPOCC was not needed.
If there will be found out more similar problems than as you suggest, manually delete all entries from the tables will be way to go. But in this case I am not sure which entries from which tables should be delete. Will deleting all from table 'kontra' automatically delete all related tables/entries/data
[tt]select * from kontra[/tt]
or other tables should be also manually deleted (but which ones)?
Any suggestion?
 
You should probably delete the post since now you have a empty entry with no connection to public.kontra

"Trying is the first step to failure..." - Homer
 
@janni78, not sure that understand your previous comment.
Even before clearing value this entry was without connection to kontra table.
Again, looking for more details regarding your suggested manually delete all entries from the tables.

 
Since you just made an "update" and emptied the field for "address" the row is still in the database and has no use.

I would delete all entries from the database and make a new import since other entries might be missing as well.

"Trying is the first step to failure..." - Homer
 
Understand. Thanks. Agree 100%.
Still looking for more detail / information how to delete all entries from the database (regarding Customers) - which tables / entries and how could be done?
Than import is easy part :)
 
You should be able to just do "delete from kontra".
That should delete all posts and the trigger should remove the references from "kontraaddress" and "kontratarget".

After you run the command on kontra just check the other tables and if there is any data in them delete those as well.

"Trying is the first step to failure..." - Homer
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top