Determining last character in field and replacing it

[weststreet]

Hello,

I have a table (Loreal) with records that contain basic information fields, i.e., Project Title, Customer service rep,  Project Number (Kp_projectID).

 

When I create a new record, the key field is auto-enter serial. However, I allow modification, BUT do not allow any entry to the field, so users can't alter the number.

 

From this table, I create a "Quote" which entails 3 related tables, and all are keyed to (Loreal)

All that works just fine.

 

If a re-quote is needed, I want to duplicate the (Loreal) record, copy the original key into the duplicated record, then add an "A" after. So going from 00012 as the original, to 00012A as the duplicate. Then I create a quote for the 00012A, and everything works, and all the keys do what they should.

 

I have been spinning my wheels, trying different combinations of functions, but no luck. This is what I have so far...that actually works.

 

  Set Variable [$projectID; Value:Loreal::__Kp_projectID]

Duplicate Record

Set Field [Loreal::__Kp_projectID; $projectID]

 

My goal is to check the $projectID last character. If it has no letter, I want to add an "A", if it has an "A" I want to replace the "A" with a "B", etc.

 

I hope this request is understandable.

[comment]

I suggest you rethink your strategy here. I don't see why you would need to duplicate the Project record just to produce another quote. If a project is allowed to have several quotes, use separate tables for Projects and Quotes, and relate them using ProjectID. 

 

 

Note:

If your table has fields for Kp_projectID and Project Title, then this is a table of Projects, where each record represents a unique project. You may prefer to call it "Loreal" or "Revlon" or "Hugo Boss" - but it's still a table of Projects, and it helps to call it what it is, esp. when you're asking for help and need other people to understand what you're dealing with.

[weststreet]

I suggest you rethink your strategy here. I don't see why you would need to duplicate the Project record just to produce another quote. If a project is allowed to have several quotes, use separate tables for Projects and Quotes, and relate them using ProjectID. 

 

I have separate tables for Project, then Quote, then Components of the project, then Quantities for each Component, then Prices for each Quantity. All are one-to-many relationships. The last table Prices is keyed to a Vendor table. 

 

A vendor may win one component in the Project, but another component may go to a different vendor. I need to track the schedule of each component as it is produced. One component now may be complete before the other. The Project information is essentially the same, but will have different vendor name (from relationship to Vendor) and schedule timeline.

I hope this is more understandable.

 

BTW, the table Project was a flat database that was used strictly for job entry and timeline tracking. Simple sorts and scripts for reporting. Now that quoting functionality has ben required, I have tried to make the relationships in a way that allows that table to remain.

[Rick Whitelaw]

"When I create a new record, the key field is auto-enter serial. However, I allow modification, BUT do not allow any entry to the field, so users can't alter the number."

This doesn't make sense to me. It seems you're disallowing field entry on the layout level. Why not prevent the user from modifying the serial on the data level?

[weststreet]

"When I create a new record, the key field is auto-enter serial. However, I allow modification, BUT do not allow any entry to the field, so users can't alter the number."

This doesn't make sense to me. It seems you're disallowing field entry on the layout level. Why not prevent the user from modifying the serial on the data level?

Hi,

I am looking to alter the number through a script, but I believe if I have the field checked as not modifiable, it can't be modified? Is that not right?

[Rick Whitelaw]

I believe if you disallow modification in the auto-enter dialog the field cannot be modified at all. If you do not do this but you disallow field entry in the Inspector then a script could still modify the value which is generally not a good idea. The auto-enter serial, when used as a primary key, should be meaningless and left alone. Not even placed on a layout in my opinion.

[weststreet]

Thanks Rick,

All understandable. 

My original question was to get a script that can check a fields last character(example"00021"). If it has no letter, I want to add an "A" to make it "00021A", if it has an "A" I want to replace the "A" with a "B", etc.

 

​I would appreciate the help. Thank You.