InfoQube IM - Community

Submitted by modeerf on 2010/05/23 14:06

I've looked and really don't see anything that addresses what I want to do.

I was a long time Ecco user but left it a few years ago and have tried everything to replace it, but have found nothing yet. I want to give IQ a shot.

I've played with it for a few weeks and I'm getting a decent idea of how to work with it, I've borrowed from other's templates and I'm really about to create 'my database' that will work for me. But I need to load some data in to get started.

I want to start with my contact database. My goal is to create a grid that will list contacts with item being lastname, firstname but the csv to import from has them both in separate field. Is it possible on the import to have it fill item with lastname without editing my underlying CSV?

thanks

Jason

Comments

Hi Jason,

I've never tried the import from an Ecco DB

But what you want to do is very simple to achieve once everything is imported.

You can either do

1- a simple copy-paste :

select the column you want to copy : ctrl-c (copy) --> select 2nd option in the copy dialog if it appears ("Selected grid rows and columns")
Select the item column (from the first row you want your "last names" to be transferred, to the last one)
Paste ("Paste each line into a separate item, creating items if necessary")

2- OR using an auto-assign rule in the lastname field (manage fields dialog : View menu -> manage fields --> select lastname field, go in the "Equation" section --> auto-assignment rules ) which will replace everything in the item's field when there's something in the the lastname field.

Something like :

AM:item=lastname

Then, just select your "lastname" column in the grid you've put it, and right click --> touch. This will activate the auto-assign rule and all last names will overwrite what's in the item field.

Words of caution :

1- if you don't need the item field to always contain the lastname's field content (when there's content), you can always turn OFF the lastname auto assignment rule (in the auto-assigment rules section of the properties pane, click on the green ON button and it will turn RED and OFF)

2- If you decide to leave the auto-assign ON, remember that filling the lastname will always erase what's in the item field. Unless you create a more sophisticated function (equation). Which I could do for you. :)

3- Another user (John?) complained about some problem with the Ecco import feature recently, I suggest you check that to see if it'll affect you or not.

Here's the link : Ecco import problems

Corrected a few spelling mistakes in last post + added link to the relevant ECCO thread.

If you have questions, don't hesitate.

(IQ is not complete yet, it will still change a bit until V1, it's still beta and has bugs (no real show stoppers right now though)... But if you're willing to make the switch and the needed efforts, I don't think you'll regret it -- it's a great and powerful app.)

Actually this isn't an ecco import, it's coming from outlook. I gave up on ecco a long time ago, and though I tried to use it again with the recent resurgence, it just still isn't what it needs to be based on the requirements I now have.

One thing I didn't make clear, was I want the item to be Lastname, Firstname.

I guess you did answer the main question though, that we can't do it during the import itself, but doing it via an auto-assignment means it's at least possible, but what about that added level of complexity using both fields?

[quote=modeerf]

One thing I didn't make clear, was I want the item to be Lastname, Firstname.

[/quote]

No problem at all... :)

What I've similarly set up in my own dB, is a simple mix of functions and auto assign rules + another field called fullname..

Follow this and tell me how it works.

1- Take note of what field you use to define what is contact.

(Why? To define my contacts, I use a "contact" field. So if you're going to use another field to define what is contact (like the addressBook field), you'll have to replace the contact field with the field name you're using in the auto assignment rules described in #3-- functions don't need to be modified).

2- Paste the functions in the IQ VB editor (view -> Visual basic editor-->This DataBase OR User Code -- depending on if you want your code to be available in all DB or just in this one), then save :

Function  fullnameToItem(contact, fullname, item)
   If contact = -1 Then 
     fullnameToItem = fullname
   Else
     fullnameToItem = item
   End If
 End Function

Function clearItem(contact,  item)
   If contact = -1 Then 
     clearitem = ""
   Else
     clearitem = item
   End If
 End Function

3- Create a field named "fullname" which will contain your LastName + FirstName (which will then be automatically copied to your item field)

To create the fullname field :

Open the manage fields dialog (view -> Manage fields),
Click on "New", And type : fullname
Choose "Text" (in the "Type" subsection of the "General" section)

The Fullname field contains these auto-assignment rules + row equation

Auto-assignment rules (In manage fields dialog, Equation section -- rewrite the "contact" word according to how this field is spelled in your own DB -- see explanations in #1):

AM:item=fullnameToItem(contact,  fullname, item) |
 E: item=clearItem(contact, item)

Row equation (In manage fields dialog, Equation section -- rewrite the "firstName" and "lastname" words according to how they are spelled in your own DB):

=FirstName & " " & LastName

What all this does is :

1- automatically fills the item field with Lastname + firstname whenever you change what's in one or the other field

2- will copy the fullname's content to the item field only if it's a contact. (This is to avoid Data Loss when the fullname's content is erased etc.)

Slightly edited previous post for better readability.

Excellent solution, and sets me up well for some other modifications I needed to make.

Thanks a lot!!

!! Very nice Armando !!
It would be a good idea, I think, to add it to the default file.

It's (superficially anyway) like the way dynamic file links work: FileFullName = FilePath + FileName
But that is done via Field>>Options:

<SOURCE>SELECT ¯qFileRef.ItemID, [FilePath] & [FileName] AS FileFullName FROM (¯qFileRef LEFT JOIN ¯qFilePath ON ¯qFileRef.ItemID = ¯qFilePath.ItemID) LEFT JOIN ¯qFileName ON ¯qFileRef.ItemID = ¯qFileName.ItemID;</SOURCE>

[quote=Tom]

[/quote]

Thanks Tom. I'm wondering if this other solution would be actually better, performance wise. Maybe not, but I'm willing to try, later this week.