Hi all,
Apologies for repeating this as I’m positive there are a million other posts for the same subject, however none seem to fit my exact requirement.
Is there any built in function in App Studio or a BPM (Low Code) to auto increment Key1, starting from 1…
I have three tables joined together as per the below;
UD07.PCRNum_c = {UD05.Key1}
UD06.PCRNum_c = {UD07.PCRNum_c} AND UD06.ParentPartID_c = {UD07.Key1}
But i do need the tables to increment based on the Key1 of UD05.
UD05 incremements from 1, 2, 3, 4, 5 etc. which is the global PCR number.
UD07 incremements 1, 2, 3, 4, 5, etc. based on the PCR number. If someone creates a record in UD07 within PCR 1 (UD05), it will have an ID of 1, 1.
UD06 incremements 1, 2, 3, 4, 5 etc. based on the UD07 number. If someone creates a record within UD07 1, it will have an ID of 1, 1, 1 etc.
I am currently using C# to do this but i don’t think its quite right. I’m seeing duplicate records created which causes a lot of issues.
**UD05 Key 1 incrementer**
// 1) Find the last added temp row (ttUD05)
var lastRow = ds.UD05
.Where(r => r.RowMod == "A") // only newly added temp rows
.OrderByDescending(r => r.Key1) // any consistent order; we just need a target row
.FirstOrDefault();
if (lastRow == null)
{
//InfoMessage.Publish("No temp row (ttUD05) found.");
return;
}
// 2) Get the max numeric Key1 from persisted DB rows (Company filter only)
int maxDb = 0;
var dbRecLatest = (from r in Db.UD05
where r.Company == Session.CompanyID
orderby r.Key1 descending
select r).FirstOrDefault();
if (dbRecLatest != null)
{
int tmp;
var rawDb = dbRecLatest.Key1 ?? string.Empty;
if (int.TryParse(rawDb, out tmp))
{
maxDb = tmp;
}
}
// 3) Get the max numeric Key1 from current temp rows (ttUD05)
int maxTemp = 0;
foreach (var r in ds.UD05)
{
// Read Key1 defensively and try parse
object rawObj = r["Key1"];
string rawTemp;
if (rawObj == null || rawObj == DBNull.Value)
rawTemp = string.Empty;
else
rawTemp = rawObj.ToString();
int tmp;
if (int.TryParse(rawTemp, out tmp))
{
if (tmp > maxTemp)
maxTemp = tmp;
}
}
// 4) Compute next Key1 (start at 1) and assign to the current temp row
int nextKey1 = Math.Max(maxDb, maxTemp) + 1;
if (nextKey1 <= 0) nextKey1 = 1; // defensiveif (nextKey1 <= 0) nextKey1 = 1; // defensive fallback
lastRow["Key1"] = nextKey1.ToString();
**UD07 Key1 incrementer**
// 1) Find the last added temp row (ttUD07)
var lastRow = ds.UD07
.Where(r => r.RowMod == "A") // only newly added temp rows
.OrderByDescending(r => r.Key1) // any consistent order; we just need a target row
.FirstOrDefault();
if (lastRow == null)
{
//InfoMessage.Publish("No temp row (ttUD07) found.");
return;
}
// 2) Get the max numeric Key1 from persisted DB rows (Company filter only)
int maxDb = 0;
var dbRecLatest = (from r in Db.UD07
where r.Company == Session.CompanyID
orderby r.Key1 descending
select r).FirstOrDefault();
if (dbRecLatest != null)
{
int tmp;
var rawDb = dbRecLatest.Key1 ?? string.Empty;
if (int.TryParse(rawDb, out tmp))
{
maxDb = tmp;
}
}
// 3) Get the max numeric Key1 from current temp rows (ttUD07)
int maxTemp = 0;
foreach (var r in ds.UD07)
{
// Read Key1 defensively and try parse
object rawObj = r["Key1"];
string rawTemp;
if (rawObj == null || rawObj == DBNull.Value)
rawTemp = string.Empty;
else
rawTemp = rawObj.ToString();
int tmp;
if (int.TryParse(rawTemp, out tmp))
{
if (tmp > maxTemp)
maxTemp = tmp;
}
}
// 4) Compute next Key1 (start at 1) and assign to the current temp row
int nextKey1 = Math.Max(maxDb, maxTemp) + 1;
if (nextKey1 <= 0) nextKey1 = 1; // defensiveif (nextKey1 <= 0) nextKey1 = 1; // defensive fallback
lastRow["Key1"] = nextKey1.ToString();
**UD07 PartID incrementer**
// Get the last added ttUD07 row
var pcrNum = ds.UD07[0].UDField<int>("PCRNum_c");
var lastRow = ds.UD07
.Where(r => r.RowMod == "A") // only newly added temp rows
.Where(r => r.UDField<int>("PCRNum_c") == pcrNum)
.OrderByDescending(r => r.Key1)
.FirstOrDefault();
//InfoMessage.Publish("PCRNum: " + pcrNum);
if (lastRow != null)
{
// 1) Latest DB record for this PCRNum (Company filter only)
var dbRec = (from r in Db.UD07
where r.Company == Session.CompanyID
&& r.PCRNum_c == pcrNum
orderby r.Key1 descending
select r).FirstOrDefault();
// 2) Determine max from DB (numeric)
int maxDb = 0;
if (dbRec != null)
{
// If ParentPartID_c is int, use directly
maxDb = dbRec.ParentPartID_c;
}
// 3) Determine max from temp rows (ttUD07) for the same PCRNum
int maxTemp = 0;
foreach (var r in ds.UD07.Where(r => r.UDField<int>("PCRNum_c") == pcrNum))
{
// Read numeric UD field safely
int tmp = 0;
try
{
tmp = r.UDField<int>("ParentPartID_c");
}
catch
{
// If field is unset or not numeric, treat as 0
tmp = 0;
}
if (tmp > maxTemp) maxTemp = tmp;
}
// 4) Next ID: max(DB, temp) + 1, starting at 1
int nextId = Math.Max(maxDb, maxTemp) + 1;
if (nextId <= 0) nextId = 1; // defensive fallback
// 5) Assign to the current temp row
lastRow["ParentPartID_c"] = nextId;
}
**UD06 Key1 incrementer**
// 1) Find the last added temp row (ttUD06)
var lastRow = ds.UD06
.Where(r => r.RowMod == "A") // only newly added temp rows
.OrderByDescending(r => r.Key1) // any consistent order; we just need a target row
.FirstOrDefault();
if (lastRow == null)
{
//InfoMessage.Publish("No temp row (ttUD06) found.");
return;
}
// 2) Get the max numeric Key1 from persisted DB rows (Company filter only)
int maxDb = 0;
var dbRecLatest = (from r in Db.UD06
where r.Company == Session.CompanyID
orderby r.Key1 descending
select r).FirstOrDefault();
if (dbRecLatest != null)
{
int tmp;
var rawDb = dbRecLatest.Key1 ?? string.Empty;
if (int.TryParse(rawDb, out tmp))
{
maxDb = tmp;
}
}
// 3) Get the max numeric Key1 from current temp rows (ttUD06)
int maxTemp = 0;
foreach (var r in ds.UD06)
{
// Read Key1 defensively and try parse
object rawObj = r["Key1"];
string rawTemp;
if (rawObj == null || rawObj == DBNull.Value)
rawTemp = string.Empty;
else
rawTemp = rawObj.ToString();
int tmp;
if (int.TryParse(rawTemp, out tmp))
{
if (tmp > maxTemp)
maxTemp = tmp;
}
}
// 4) Compute next Key1 (start at 1) and assign to the current temp row
int nextKey1 = Math.Max(maxDb, maxTemp) + 1;
if (nextKey1 <= 0) nextKey1 = 1; // defensiveif (nextKey1 <= 0) nextKey1 = 1; // defensive fallback
lastRow["Key1"] = nextKey1.ToString();
**UD06 CompID incrementer**
var pcrNum = ds.UD06[0].UDField<int>("PCRNum_c");
var ppi = ds.UD06[0].UDField<int>("ParentPartID_c");
// Find the last added temp row
var lastRow = ds.UD06
.Where(r => r.RowMod == "A")
.Where(r => r.UDField<int>("PCRNum_c") == pcrNum)
.Where(r => r.UDField<int>("ParentPartID_c") == ppi)
.OrderByDescending(r => r.Key1)
.FirstOrDefault();
if (lastRow == null)
return;
// 1) Max from DB
int maxDb = 0;
var dbRecLatest = (from r in Db.UD06
where r.Company == Session.CompanyID
&& r.PCRNum_c == pcrNum
&& r.ParentPartID_c == ppi
orderby r.Key1 descending
select r).FirstOrDefault();
if (dbRecLatest != null)
{
// TechnicalCompId_c is already an int
maxDb = dbRecLatest.TechnicalCompId_c;
}
// 2) Max from temp (ds.UD06)
int maxTemp = 0;
foreach (var r in ds.UD06.Where(x =>
x.UDField<int>("PCRNum_c") == pcrNum &&
x.UDField<int>("ParentPartID_c") == ppi))
{
// TechnicalCompId_c is already an int field in the ds
int tmp = r.UDField<int>("TechnicalCompId_c");
if (tmp > maxTemp)
maxTemp = tmp;
}
int nextId = Math.Max(maxDb, maxTemp) + 1;
// Set next ID into the current row
lastRow["TechnicalCompId_c"] = nextId;
Thanks,
James