How to convert a number to text?

[Rochelle_505]

Hello all,

How can I convert a field in my layout to text from a number?

I am trying to convert a customer # to the customer name and have the name populate the field...Is this possible? I guess I am trying to have the report grab the customer # and then convert the number to the customer's name and put that in the field name "customer name".....please help.....

[-Queue-]

Um, why not just use the related customer name field on your report? If it's more complicated then that, you can use a lookup or a calculation field equal to the related name field.

[Rochelle_505]

I have been going crazy over this, because I tried using the name field and no matter what I do, the name will not populate the field. I have tried different relationships and look ups with no success. The field will show the customer number, but when I try to specify the customer name field, it won't show up at all. At wit's end....

[RalphL]

Customer number & Customer name are 2 different fields. You must have a realtionship to customer using Customer number. Using that relationship Customer name can be obtained by a related field, a calculation or a lookup. If you want to give the apperance that the customer number field changed to customer name place the customer name field on top of the customer number. It will have to have a solid background color too hide the number. Do not allow users to edit the name field and remove it from the tab order.

[Rochelle_505]

Thanks so much...

I will give it a try.....

[Rochelle_505]

I gave it a try, but to no avail.The customer name is still being elusive...My objective here is to have the customer name and info come up on a report in my line items database, and no matter what I try only the customer number will come up...These two fields are in the same database, but only the customer number is coming up..I have a relationship from the line item database to customer database by customer number and vice versa. What else can I try?

[-Queue-]

When in Layout Mode in Line Items, add a field, select the customer relationship and the related customer name field. If this doesn't work, post a sample of your file.

[Rochelle_505]

It didn't work.....Here are my relationships..

Relationships2.zip

[Barbecue]

Rochelle, you only included one file, so the relationship can't be seen.

[Rochelle_505]

I did a screenshot, hope it works for you...

Relationshipsscreenshot.zip

[-Queue-]

It would be more helpful to see the actual dbs. If you could create clones of the two that are your problem and post them, we could resolve this more quickly.

[Rochelle_505]

I have made clones of my db's,but had to upload them separately. Could the problem be that the customer name field is from imported records through an as/400 odbc and the information is old???

thanks so much for all your help....

customerdatabaseClone.zip

[Rochelle_505]

Here is the 2nd db...

invoicCo2CopyClone.zip

[Rochelle_505]

this is the problem db, layout 3 and the supplier report layout, the customer name will not populate the customer name field...

LIneItemClone.zip

[-Queue-]

FYI, you could have included the three files in a single zip instead of spreading them over three posts.

In your Invoice file, you're using the Customer Name field from the customer db. Customer Name is never entered in the invoice file. So there's no way to view it in your Line Items file, unless you use the same field from customer db.

[Rochelle_505]

Sorry I did try to put all three in one zip and tested it,but it seemed like it combined all three into one, and then was asking for the other two to open the first one.....I didn't have the time to troubleshoot...

Going back to my crazy db's, I tried using customer name from the customer db, but that did not work...Is my only option to tell the data enterer that they will have to add both the customer name and customer number? I know it sounds stupid but they are trying to cut down on data entry time...Is there a way to have the customer name field populate the name after the customer number is keyed in??? I know the supplier field is set up this way..thanks again-Q-

[-Queue-]

Yes, use the customer db::customer name field, where customer db is a relationship based on customer id, or have the customer name field in invoices lookup the customer name from customer db and use invoices::customer name, as you already were. Either way will work as long as there is a related record with an entered customer name. It can also be either the related field itself or a lookup to the related field.

[Rochelle_505]

I will give it a try...cross your fingers

[Rochelle_505]

Okay getting the customer field to populate...

But now when I run the report the customer name is staying the same for every invoice....almost there!!

[Rochelle_505]

The customer name is populating the field, but it is showing the same name for every record....Do I need to make another relationship?

[-Queue-]

You need to use a relationship that is unique for each record, in the sense that two different customer numbers produce two different names. What does your relationship match?

[Rochelle_505]

The relationships are:

Customer Table (2)

Customer#::Customer# related table Invoice

CustomerName::CustomerName related table Invoice

Line Item Table (3)

Customer#::Customer# related table Customer

Customer#::Customer# related table Invoice

Customername::Customername related table Invoice

Invoice Table Master file

Customer#::Customer# related table Customer

[-Queue-]

Using the relationships based on Customer::Customer# in the Line Items or Invoices table should produced the desired results. I'm curious why you have any based on CustomerName, unless you are using them to select the customer from a value list or portal.

This really should not be too complicated of a problem. I would suggest that you purchase a book that will help you understand relationships and give you a better grasp on what you are doing. I think we are going to be going back and forth on this, otherwise, and there is no reason it should take three virtual pages to settle this issue. I do not know how else to help you, except to repeat the same things I've already said. So I think this would be the most beneficial course of action to take.

[Rochelle_505]

I used customer name out of desperation to see if it would help, but it didn't do anything..One complaint I do have is that FM does not have a diagram available to let you actually see what type of relationships you have, (one-one,one-many,many-many) like Access does, that would take away a lot of the guesswork. Thanks for all your help so far....

[-Queue-]

A relationship based on Customer Name would only work if you were entering the Customer Name, which is what you are trying to avoid doing...

You should upgrade to 7 for a completely different view of relationships. But you can somewhat assume the type of relationship in 6. If it's a self-relationship based on a unique serial, then it's one-to-one. If it's a primay key to a foreign key, then it could be either one-to-one or one-to-many, depending on how you use it. If you include a flag in your relationship, such as 'JoinTable' or 'JT' , you'll know it's part of a many-to-many.