Jump to content

Determining last character in field and replacing it


This topic is 2836 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

"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?

Link to comment
Share on other sites

"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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2836 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.