Creating serial number into portal - comp.databases.filemaker

Hi I try to make an auto-entry field number go from 10 to 20,30,40 and so one for every record create in that portal. but this need to be reset to 10 when the user create data in that portal for a different record, and every time we add something to an existing portal, number will increase by 10 from the highest number for that releated record. I've try this into auto-enter calcalation value Entretien is the main layout table Entretien_E is the table in the portal Entretien_E_Self is a selfjoint table to be able to find the Max value Case ( IsValid (Entretien_E_Selft::EE_No_List);10; // this is supose to return TRUE if there no data in the related table Max ( Entretien_E_Self::EE_No_List)+10) // this is supose to take the MAX value of EE_No_List and add 10 to it's value. But every time I create a new value in the portal, I get "10" as a result in the field EE_No_List in the Table Entretien_E Any clue how can I make my thing to work ?? and I can't use a fixed calculation field, because later user can change those number manually (I use those number to sort in the display portal list of equipment) so user must have acces to change order, due to equipments location..... Thank you JF

In article <CJRPh.8565$ig1.52217@weber.videotron.net>, "JF" <bizouman_takethisout@hotmail.com> wrote: > Hi > > I try to make an auto-entry field number go from 10 to 20,30,40 and so one > for every record create in that portal. but this need to be reset to 10 when > the user create data in that portal for a different record, and every time > we add something to an existing portal, number will increase by 10 from the > highest number for that releated record. > > I've try this into auto-enter calcalation value > > Entretien is the main layout table > Entretien_E is the table in the portal > Entretien_E_Self is a selfjoint table to be able to find the Max value > > Case ( > IsValid (Entretien_E_Selft::EE_No_List);10; // this is supose to > return TRUE if there no data in the related table > Max ( Entretien_E_Self::EE_No_List)+10) // this is supose to take > the MAX value of EE_No_List and add 10 to it's value. > > But every time I create a new value in the portal, I get "10" as a result in > the field EE_No_List in the Table Entretien_E > > Any clue how can I make my thing to work ?? and I can't use a fixed > calculation field, because later user can change those number manually (I > use those number to sort in the display portal list of equipment) so user > must have acces to change order, due to equipments location..... Don't use IsValid. You've got it backwards. IsValid will return True if there IS a related record, not when there is not. You could use Not(IsValid(Entretien_E_Selft::EE_No_List)) instead, but why bother ... simply Count the number of related records. eg. Case(Count(Entretien_E_Selft::EE_No_List) = 0, 10, Max(Entretien_E_Selft::EE_No_List) + 10 ) This assumes that the Entretien_E_Selft relationship is grouping records of the same type and that EE_No_List is the field that actually contains the previous serial numbers. Helpful Harry Hopefully helping harassed humans happily handle handiwork hardships ;o)

OK, I try several idea for finding out that there's no data in the relationship so the fist entry will be "10". This work every time..... it's the next generated number that never work...... I try you idea and always the same result, all the new record in the portal got "10" as an auto-entry value.... What I'm doing wrong ?? It look like the entry in the portal is not seeing other values or when typing data is not looking for more then one record, as the new one is alone in the relationship ?!??! JF "Helpful Harry" <helpful_harry@nom.de.plume.com> a �crit dans le message de news: 020420070904423154%helpful_harry@nom.de.plume.com... > In article <CJRPh.8565$ig1.52217@weber.videotron.net>, "JF" > <bizouman_takethisout@hotmail.com> wrote: > >> Hi >> >> I try to make an auto-entry field number go from 10 to 20,30,40 and so >> one >> for every record create in that portal. but this need to be reset to 10 >> when >> the user create data in that portal for a different record, and every >> time >> we add something to an existing portal, number will increase by 10 from >> the >> highest number for that releated record. >> >> I've try this into auto-enter calcalation value >> >> Entretien is the main layout table >> Entretien_E is the table in the portal >> Entretien_E_Self is a selfjoint table to be able to find the Max value >> >> Case ( >> IsValid (Entretien_E_Selft::EE_No_List);10; // this is supose to >> return TRUE if there no data in the related table >> Max ( Entretien_E_Self::EE_No_List)+10) // this is supose to >> take >> the MAX value of EE_No_List and add 10 to it's value. >> >> But every time I create a new value in the portal, I get "10" as a result >> in >> the field EE_No_List in the Table Entretien_E >> >> Any clue how can I make my thing to work ?? and I can't use a fixed >> calculation field, because later user can change those number manually (I >> use those number to sort in the display portal list of equipment) so user >> must have acces to change order, due to equipments location..... > > Don't use IsValid. You've got it backwards. IsValid will return True if > there IS a related record, not when there is not. > > You could use Not(IsValid(Entretien_E_Selft::EE_No_List)) instead, but > why bother ... simply Count the number of related records. > eg. > Case(Count(Entretien_E_Selft::EE_No_List) = 0, 10, > Max(Entretien_E_Selft::EE_No_List) + 10 > ) > > This assumes that the Entretien_E_Selft relationship is grouping > records of the same type and that EE_No_List is the field that actually > contains the previous serial numbers. > > > Helpful Harry > Hopefully helping harassed humans happily handle handiwork hardships ;o)

"JF" <bizouman_takethisout@hotmail.com> schreef in bericht news:m8WPh.19902$ig1.106667@weber.videotron.net... > OK, I try several idea for finding out that there's no data in the > relationship so the fist entry will be "10". > > This work every time..... it's the next generated number that never > work...... I try you idea and always the same result, all the new record > in the portal got "10" as an auto-entry value.... > > What I'm doing wrong ?? It look like the entry in the portal is not seeing > other values or when typing data is not looking for more then one record, > as the new one is alone in the relationship ?!??! > > JF > > > "Helpful Harry" <helpful_harry@nom.de.plume.com> a �crit dans le message > de news: 020420070904423154%helpful_harry@nom.de.plume.com... >> In article <CJRPh.8565$ig1.52217@weber.videotron.net>, "JF" >> <bizouman_takethisout@hotmail.com> wrote: >> >>> Hi >>> >>> I try to make an auto-entry field number go from 10 to 20,30,40 and so >>> one >>> for every record create in that portal. but this need to be reset to 10 >>> when >>> the user create data in that portal for a different record, and every >>> time >>> we add something to an existing portal, number will increase by 10 from >>> the >>> highest number for that releated record. >>> >>> I've try this into auto-enter calcalation value >>> >>> Entretien is the main layout table >>> Entretien_E is the table in the portal >>> Entretien_E_Self is a selfjoint table to be able to find the Max value >>> >>> Case ( >>> IsValid (Entretien_E_Selft::EE_No_List);10; // this is supose >>> to >>> return TRUE if there no data in the related table >>> Max ( Entretien_E_Self::EE_No_List)+10) // this is supose to >>> take >>> the MAX value of EE_No_List and add 10 to it's value. >>> >>> But every time I create a new value in the portal, I get "10" as a >>> result in >>> the field EE_No_List in the Table Entretien_E >>> >>> Any clue how can I make my thing to work ?? and I can't use a fixed >>> calculation field, because later user can change those number manually >>> (I >>> use those number to sort in the display portal list of equipment) so >>> user >>> must have acces to change order, due to equipments location..... >> >> Don't use IsValid. You've got it backwards. IsValid will return True if >> there IS a related record, not when there is not. >> >> You could use Not(IsValid(Entretien_E_Selft::EE_No_List)) instead, but >> why bother ... simply Count the number of related records. >> eg. >> Case(Count(Entretien_E_Selft::EE_No_List) = 0, 10, >> Max(Entretien_E_Selft::EE_No_List) + 10 >> ) >> >> This assumes that the Entretien_E_Selft relationship is grouping >> records of the same type and that EE_No_List is the field that actually >> contains the previous serial numbers. >> >> >> Helpful Harry >> Hopefully helping harassed humans happily handle handiwork hardships ;o) > > How on earth is an auto-entry field going to know a value from a previous record? On creation everything except auto-generated data is empty, probably the relation is empty at the time the field auto generates. So no values are counted and the calculation will allways return 10. In this case a script and a button might come in handy. This creates the record, fills in anything you need, then looks if the relation is valid and fills the field accordingly. If you don't like that I would recommend a script trigger like zipscript. This would take out the button and automates the triggering of the script. Keep well, Ursus

In article <46103978$0$26404$dbd41001@news.wanadoo.nl>, "Ursus" <ursus.kirk@wanadoo.nl> wrote: > "JF" <bizouman_takethisout@hotmail.com> schreef in bericht > news:m8WPh.19902$ig1.106667@weber.videotron.net... > > "Helpful Harry" <helpful_harry@nom.de.plume.com> a �crit dans le message > > de news: 020420070904423154%helpful_harry@nom.de.plume.com... > >> In article <CJRPh.8565$ig1.52217@weber.videotron.net>, "JF" > >> <bizouman_takethisout@hotmail.com> wrote: > >> > >>> Hi > >>> > >>> I try to make an auto-entry field number go from 10 to 20,30,40 and so > >>> one > >>> for every record create in that portal. but this need to be reset to 10 > >>> when > >>> the user create data in that portal for a different record, and every > >>> time > >>> we add something to an existing portal, number will increase by 10 from > >>> the > >>> highest number for that releated record. > >>> > >>> I've try this into auto-enter calcalation value > >>> > >>> Entretien is the main layout table > >>> Entretien_E is the table in the portal > >>> Entretien_E_Self is a selfjoint table to be able to find the Max value > >>> > >>> Case ( > >>> IsValid (Entretien_E_Selft::EE_No_List);10; // this is supose > >>> to > >>> return TRUE if there no data in the related table > >>> Max ( Entretien_E_Self::EE_No_List)+10) // this is supose to > >>> take > >>> the MAX value of EE_No_List and add 10 to it's value. > >>> > >>> But every time I create a new value in the portal, I get "10" as a > >>> result in > >>> the field EE_No_List in the Table Entretien_E > >>> > >>> Any clue how can I make my thing to work ?? and I can't use a fixed > >>> calculation field, because later user can change those number manually > >>> (I > >>> use those number to sort in the display portal list of equipment) so > >>> user > >>> must have acces to change order, due to equipments location..... > >> > >> Don't use IsValid. You've got it backwards. IsValid will return True if > >> there IS a related record, not when there is not. > >> > >> You could use Not(IsValid(Entretien_E_Selft::EE_No_List)) instead, but > >> why bother ... simply Count the number of related records. > >> eg. > >> Case(Count(Entretien_E_Selft::EE_No_List) = 0, 10, > >> Max(Entretien_E_Selft::EE_No_List) + 10 > >> ) > >> > >> This assumes that the Entretien_E_Selft relationship is grouping > >> records of the same type and that EE_No_List is the field that actually > >> contains the previous serial numbers. > > > > OK, I try several idea for finding out that there's no data in the > > relationship so the fist entry will be "10". > > > > This work every time..... it's the next generated number that never > > work...... I try you idea and always the same result, all the new record > > in the portal got "10" as an auto-entry value.... > > > > What I'm doing wrong ?? It look like the entry in the portal is not seeing > > other values or when typing data is not looking for more then one record, > > as the new one is alone in the relationship ?!??! Are you sure you're using the correct field for the self-relationship?? It should be a field that contains the same data as other records in the Portal. For example, if the Portal relationship is using MyID to link the related records to the parent record, then the self-relationship should also use the MyID field. > How on earth is an auto-entry field going to know a value from a previous > record? On creation everything except auto-generated data is empty, probably > the relation is empty at the time the field auto generates. So no values are > counted and the calculation will allways return 10. In this case a script > and a button might come in handy. This creates the record, fills in anything > you need, then looks if the relation is valid and fills the field > accordingly. If you don't like that I would recommend a script trigger like > zipscript. This would take out the button and automates the triggering of > the script. Ah, but you can force an auto-enter field to not calculate until the necessary data is ready. For example, if MyID is the field being used to define the self-relationship, then the SerialNum field's auto-enter calculation can first check to make sure that has data before it calculates. ie. Case (IsEmpty(MyID), "", Case(Count(Entretien_E_Selft::EE_No_List) = 0, 10, Max(Entretien_E_Selft::EE_No_List) + 10 ) ) It's a neat trick that leaves the SerialNum field empty until MyID contains data ... because SerialNum is empty, the auto-enter calculation will be calculated as soon as MyID has data in it. There is an option in the bottom of the define calculation window that is meant to do something similar, but it never appears to work as it should. Helpful Harry Hopefully helping harassed humans happily handle handiwork hardships ;o)

Harry your the best !! It's working, but there's still a small problem..... when entering more then 1 data in the portal row, since the data is not commit at the end of the portal row, it jumps to the next row to continue adding info, but the MAX value is still the same as the previous one, so I end up creating many row with "20" if I commit record, then the next portal will add up 10, that work fine........ Maybe I will use Ursus idea to make a script and a field that trigger the modification..... but you see that's what I don't like about FM some time, easy task can make you create script and calculation field by dozens.....and since all script are in the same file now.... that about 300 for me right now..... the list is geting longer !! If you have another idea, it will be welcome :O) JF "Helpful Harry" <helpful_harry@nom.de.plume.com> a �crit dans le message de news: 020420071226328697%helpful_harry@nom.de.plume.com... > In article <46103978$0$26404$dbd41001@news.wanadoo.nl>, "Ursus" > <ursus.kirk@wanadoo.nl> wrote: > >> "JF" <bizouman_takethisout@hotmail.com> schreef in bericht >> news:m8WPh.19902$ig1.106667@weber.videotron.net... >> > "Helpful Harry" <helpful_harry@nom.de.plume.com> a �crit dans le >> > message >> > de news: 020420070904423154%helpful_harry@nom.de.plume.com... >> >> In article <CJRPh.8565$ig1.52217@weber.videotron.net>, "JF" >> >> <bizouman_takethisout@hotmail.com> wrote: >> >> >> >>> Hi >> >>> >> >>> I try to make an auto-entry field number go from 10 to 20,30,40 and >> >>> so >> >>> one >> >>> for every record create in that portal. but this need to be reset to >> >>> 10 >> >>> when >> >>> the user create data in that portal for a different record, and every >> >>> time >> >>> we add something to an existing portal, number will increase by 10 >> >>> from >> >>> the >> >>> highest number for that releated record. >> >>> >> >>> I've try this into auto-enter calcalation value >> >>> >> >>> Entretien is the main layout table >> >>> Entretien_E is the table in the portal >> >>> Entretien_E_Self is a selfjoint table to be able to find the Max >> >>> value >> >>> >> >>> Case ( >> >>> IsValid (Entretien_E_Selft::EE_No_List);10; // this is >> >>> supose >> >>> to >> >>> return TRUE if there no data in the related table >> >>> Max ( Entretien_E_Self::EE_No_List)+10) // this is supose >> >>> to >> >>> take >> >>> the MAX value of EE_No_List and add 10 to it's value. >> >>> >> >>> But every time I create a new value in the portal, I get "10" as a >> >>> result in >> >>> the field EE_No_List in the Table Entretien_E >> >>> >> >>> Any clue how can I make my thing to work ?? and I can't use a fixed >> >>> calculation field, because later user can change those number >> >>> manually >> >>> (I >> >>> use those number to sort in the display portal list of equipment) so >> >>> user >> >>> must have acces to change order, due to equipments location..... >> >> >> >> Don't use IsValid. You've got it backwards. IsValid will return True >> >> if >> >> there IS a related record, not when there is not. >> >> >> >> You could use Not(IsValid(Entretien_E_Selft::EE_No_List)) instead, but >> >> why bother ... simply Count the number of related records. >> >> eg. >> >> Case(Count(Entretien_E_Selft::EE_No_List) = 0, 10, >> >> Max(Entretien_E_Selft::EE_No_List) + 10 >> >> ) >> >> >> >> This assumes that the Entretien_E_Selft relationship is grouping >> >> records of the same type and that EE_No_List is the field that >> >> actually >> >> contains the previous serial numbers. >> > >> > OK, I try several idea for finding out that there's no data in the >> > relationship so the fist entry will be "10". >> > >> > This work every time..... it's the next generated number that never >> > work...... I try you idea and always the same result, all the new >> > record >> > in the portal got "10" as an auto-entry value.... >> > >> > What I'm doing wrong ?? It look like the entry in the portal is not >> > seeing >> > other values or when typing data is not looking for more then one >> > record, >> > as the new one is alone in the relationship ?!??! > > Are you sure you're using the correct field for the self-relationship?? > It should be a field that contains the same data as other records in > the Portal. For example, if the Portal relationship is using MyID to > link the related records to the parent record, then the > self-relationship should also use the MyID field. > > > >> How on earth is an auto-entry field going to know a value from a previous >> record? On creation everything except auto-generated data is empty, >> probably >> the relation is empty at the time the field auto generates. So no values >> are >> counted and the calculation will allways return 10. In this case a script >> and a button might come in handy. This creates the record, fills in >> anything >> you need, then looks if the relation is valid and fills the field >> accordingly. If you don't like that I would recommend a script trigger >> like >> zipscript. This would take out the button and automates the triggering of >> the script. > > Ah, but you can force an auto-enter field to not calculate until the > necessary data is ready. For example, if MyID is the field being used > to define the self-relationship, then the SerialNum field's auto-enter > calculation can first check to make sure that has data before it > calculates. > ie. > Case (IsEmpty(MyID), "", > Case(Count(Entretien_E_Selft::EE_No_List) = 0, 10, > Max(Entretien_E_Selft::EE_No_List) + 10 > ) > ) > > It's a neat trick that leaves the SerialNum field empty until MyID > contains data ... because SerialNum is empty, the auto-enter > calculation will be calculated as soon as MyID has data in it. > > There is an option in the bottom of the define calculation window that > is meant to do something similar, but it never appears to work as it > should. > > > Helpful Harry > Hopefully helping harassed humans happily handle handiwork hardships ;o)

In article <D0ZPh.26686$ig1.153111@weber.videotron.net>, "JF" <bizouman_takethisout@hotmail.com> wrote: > Harry your the best !! > It's working, but there's still a small problem..... when entering more then > 1 data in the portal row, since the data is not commit at the end of the > portal row, it jumps to the next row to continue adding info, but the MAX > value is still the same as the previous one, so I end up creating many row > with "20" if I commit record, then the next portal will add up 10, that work > fine........ Maybe I will use Ursus idea to make a script and a field that > trigger the modification Sorry, I'm still using older versions of FileMaker that don't know much about that new commit nonsense... err, "feature". Although I can see an ocassional use for it, it should be something that can be turned off via a Document Preference option. > ..... but you see that's what I don't like about FM > some time, easy task can make you create script and calculation field by > dozens.....and since all script are in the same file now.... that about 300 > for me right now..... the list is geting longer !! This is one problem of the "all in one file" form of database design that newer versions of FileMaker let you make. I've created lots of database systems, but I don't think any single file has ever got to 100 scripts - and entire system may have lots, bit they're nicely separated in individual files along with all the fields, etc. that they belong to. Helpful Harry Hopefully helping harassed humans happily handle handiwork hardships ;o)