finding serial number

[Ben Ball]

I have a field with is a auto entry field.. which has a relation to a unique serial number.

The Serial number starts with P0000 and increments by 1.

I have a script that needs to check for the existence of the P number... how do I do this?

If the serial number is say P1024.... I just need to know if there is a P number there.

[LaRetta]

Hi Ben,

I would hate to answer because I don't yet understand the question. YOu give us some details but not enough to figure out what you are doing or what you really want. If you have an auto-enter field in one table, how can it relate to a unique serial in another table? And if it relates, why are you needing to check if it exists, ie, why does script need to check for existence of P number?

I suspect you are in trouble and probably don't even realize it yet. Please say more about what you are doing, how they are related and the purpose of all of this and we'll help you further.

LaRetta :wink2:

[Fitch]

I think he's trying to see if a record in one table matches a record in another table.

IsEmpty( related::Pnumber ) will evaluate to "1" if there's no matching record (where "related" is the name of your Table Occurrence and "Pnumber" is the name of your serial number).

[Ben Ball]

Fitch I think you understand my concept.

But the problem is.... The field will not be empty as it is used as a status box until it gets filled with a "Pnumber" so it shows different status positions up until the related table has a record created which has the unique Pnumber.

Thus... I have table 1 which is my parent... this has Table1:relatedfield_Pnumber I have a child table2 with a UniqueID_PNumber.

When a record is created in table2.... it posts its unique ID number into the parent table1::relatedfield_Pnumber. ONly one parent... one child record is ever created.

So... if this record is revisited... it will look if a Pnumber has already been created before creating another new record in Table2....

so.... if there is a Pnumber.... I go to that related record in table 2... from table 1... as opposed to creating a new record.

As there are multible records being shown in the portal. I need to check if a Pnumber is there or not.... but I cannot check for "isempty"...

I need to be able to check for existence of the P before the number... P1234... how can I single out the P number?

Ben

[LaRetta]

I believe it would work better to not combine your serial with a prefix which means something. Because you will then need to 'remove' that prefix to get your relationship. But then again, I still have no idea what you are doing or why.

So, to remove the prefix from P1234, you must use a calculation in that table, something like: Substitute ( thatField ; "P" ; "" ) and then use THAT in your join. Or even

Right ( thatField ; Length ( thatField ) - 1 ) but I have no idea what that 'P' represents or whether different letters will appear or in different locations!

The field will not be empty as it is used as a status box until it gets filled with a "Pnumber" so it shows different status positions up until the related table has a record created which has the unique Pnumber.

I think you are way-overcomplicating your relational keys and your process. Can you provide a sample of what you are doing and why? It might help us. :wink2:

[Ben Ball]

Well the problem is... very complicated. But there's nothing I can really post up... but basically it will always just be a "p" in front of the serial number... so it starts of P0000 then will index by 1 every time... so p will always be in the same place.

All I am doing is posting this Pnumber into a field so the two tables/records have a relationship.

So it seems your calc field will get me what I am looking for... I understand that it may seem complicated... it is kind of very complicated.. but not this particular relationship... its a straight forward.... unique serial number to text field relationship.

thanks for your help.... I will try this and if no luck I will mock my problem and post it up here

[Ugo DI LUCA]

Well the problem is... very

All I am doing is posting this Pnumber into a field so the two tables/records have a relationship.

Hi,

Not sure I fully understand the problem here. But IsEmpty ( MyRelationship::MySerial ), as Tom suggested should work.

IsEmpty ( ) not only looks if the referenced field is empty, which as you said would happen to be true at any time. It simply check through the referenced relation if there's a matching key, so if a valid relation exists.

So, I don't get why it wouldn't work.

[LaRetta]

It won't work because he is first filling it with another value. Which was why I kept trying to understand why. It contains the STATUS value until it is turned into a P number and I still don't get it. I again question what and why and I doubt the logic of the solution, but I'm giving up trying to bring into the light.

Simply, the serial should NOT be used as a value meaning something like status - it should be left pure.

Edited October 18, 2007 by Guest

[comment]

It won't work because he is first filling it with another value.

But if the value is not a match, the record is not related - so IsEmpty ( related::Pnumber ) would still return true.

---

I agree that this doesn't sound like a good arrangement to have, but to be sure I would have to understand it first.

Edited October 18, 2007 by Guest

[Fitch]

If you have a set of related values, and you want to see if one of them has a "P" in it, you could make a calculated field in that table:

Position( Pnumber ; "P" ; 1; 1 )

Let's call that field "hasP". It will be empty in all the records except the one with a "P", where it will be 1.

Then in the parent table, use:

IsEmpty( related::hasP )

[LaRetta]

Tom, you and Michael are both right. But what happens to that P number field, which also contains a status when NOT a P number, especially if they later add a status of Pending? We can speculate and make various suggestions but (at least to me) the best suggestion is: CLARIFY because major red lights are flashing for me. We still have NO vision here ... none. We don't even know what these tables represent.

Ben may have 114 posts but the pattern I see is too little information and not always feedback whether something worked after suggestions are made like here and here .

I was trying to avoid playing "Pin the Answer on the Donkey."

You guys are much better at the game and obviously have more energy than I, so I'll let you take it.

[Ugo DI LUCA]

or

not PatternCount ( List ( MyRelatedTO::serial ) ; "P" )

with no need for a calc in the related TO

But I still don't have a clue about this status thing.

Edited October 18, 2007 by Guest

[LaRetta]

Well, I can't help it. I find it quite funny that we're all helping Ben so much and we truly don't understand what he needs and, he thinks Tom might have helped him. But you'all keep questioning ... and I'm in the background yelling, "Fire! Fire!" and ... it tickles me.

The field will not be empty as it is used as a status box until it gets filled with a "Pnumber" so it [color:red]shows different status positions up until the related table has a record created which has the unique Pnumber.

Fire! Ha Ha!! Aren't we a wonderful bunch?!

Edited October 18, 2007 by Guest

[Ugo DI LUCA]

Post Status : Pending :)

[Ben Ball]

OK..... I will create a database here and show you what I am doing....

Its the least I can do to at least give you some more idea... and especially as I have caused a few people to get involved...

I appreciate the help here.... but the calc field is the best idea to solve my problem. Then I can use the "isempty" command....

Keep I eye out... I will post as soon as I can.

Thanks guys

Ben

[Ben Ball]

OK, Here it is....

I made a version of my problem.

I hope you can see what my problem is.?

I have included a solution which has a soloution added by UGO Di Luca.

If I can doing something drastically wrong here... then please can someone tell me.. I mean there is always more than one way to skin a cat... but if its not good practice... then I need to know.

Thanks for all the time people.

Ben

PS... I had to post another file up... I noticed I had an incorrect relationship for my GTRR script step...

example_1.zip

Edited October 23, 2007 by Guest